Math in Views

[edmana]

I have a view which is working fine, something like this:

Select a,b,c,d,a*b as AB, c*d as CD, a*b*c*d as ABCD
From tblX

Is there an easier way to do the more complex math (a*b*c*d), such as AB*CD?

I tried using AB*CD as ABCD and it didn't work (error).

Keep in mind this is a really dumbed down version of the full query but it is easier to read than the actual one...

Thanks!
Ed

 

[RiverGuy]

Not really. But you could use derived tables if you want:

SELECT one.*, AB*CD AS ABCD
FROM

(SELECT a, b, c, d, a*b AS AB, c*d AS CD
FROM tblX) one

 

[gmmastros]

The answer is.... yes and no (kinda).

Let's take a closer look.

[tt][blue]
Select a,b,c,d,
a*b [!]as AB[/!],
c*d [!]as CD[/!],
a*b*c*d [!]as ABCD[/!]
From tblX
[/blue][/tt]

The parts highlighted in red are called column aliases. You cannot use a column alias in your query. You can 'get out of' your query by using a derived table, like this...

[tt][blue]
Select a,b,c,d,AB,CD, TableAlias.AB * TableAlias.CD As ABCD
From (
Select a,b,c,d,
a*b as AB,
c*d as CD
From tblX
) As TableAlias
[/blue][/tt]

Derived tables allow this to work because the derived table part is evaluated before the outer query is executed (or... at least you can think of it that way).

Notice that the outer query is no longer selecting from tblX. It's now selecting from the derived table TableAlias.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[edmana]

Could I do this as a stored procedure instead? Would I have a better chance w/ the math there?

 

[gmmastros]

You would have the same issues if this was done in a stored procedure.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom