Calculate field in a view

Question

Post reply

Calculate field in a view

bob-685288

SSC Rookie

Points: 37
More actions
April 12, 2008 at 7:51 pm

#181394

I have been able crate a query that takes a balance column and provides a total using Compute, however I need to do the same thing in a view which does not accept Compute. Any help would be greatly appreciated.
As this is a generic question I have not added code, but I am working with one table only. It contains a balance field,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Well, generically, you do not need COMPUTE.

If you would like a more specific answer, you might want to give us a more specific example to work with.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Heh... yeah... and some generic data with a generic table structure along with your generic code and a generic sample of what the output should look like would be a generically good idea. 😉

See the URL in my signature line, please.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

bob-685288 SSC Rookie Points: 37 More actions · Answer 3

With apologies and thanks in advance, I am using SQL2005

Here is the query

Select Acct_num AS 'GL Number', Acct_desc AS Item,

Acct_short_desc Description, BALANCE--What we want displayed

From Chart_Of_Accounts--Name of the table

Where BALANCE != 0--We want only those fields that have a trial balance

ORDER BY Acct_num, Acct_desc, Acct_short_desc

COMPUTE SUM(BALANCE)Give us a grand total

The table contains only those fields in the select statement and and it is the only one table in the db

What I would like is to create a View that diplays the same information as the query, but I add Create View AS xyz

I get a Incorrect syntax near the keyword 'COMPUTE' message. I have read you can't use compute in Views but I need to compute the total.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Well, there are a number of ways to go with this, a UNION being the most obvious and some folks use those fancy OVER-based functions, however I like this one:

Create View vwAccountBalances

AS

Select Top 100000

Case Grouping(Acct_Num) When 0 Then Acct_num Else 'grand total' End AS 'GL Number'

, Acct_desc AS Item

, Acct_short_desc Description

, SUM(BALANCE)--What we want displayed

From Chart_Of_Accounts--Name of the table

Where BALANCE != 0--We want only those fields that have a trial balance

Group By Acct_num, Acct_desc, Acct_short_desc

With ROLLUP

HAVING ( Grouping(Acct_num)=0 And Grouping(Acct_desc)=0 and Grouping(Acct_short_desc)=0 )

OR ( Grouping(Acct_num)=1 And Grouping(Acct_desc)=1 and Grouping(Acct_short_desc)=1 )

ORDER BY Acct_num, Acct_desc, Acct_short_desc

NOTE: you haven't gotten to it yet,but the ORDER BY is invalid in Views also, unless you have a TOP clause. You shoud note, however that this does not *guarantee* that the order will be preserved, so the ORDER BY is better done by the user of the View.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

As Barry has shown, ROLLUP should do it. For more information, lookup GROUPING under CUBE in Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

bob-685288 SSC Rookie Points: 37 More actions · Answer 6

I thank both of you, as a new user of SQL I new what I wanted, just didn't know the terminology to find it.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Glad we could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]