How to use calculated fields in sql server views?

[Alibaba2003]

hi .. this maybe an easy solution:
i want to convert this simple access query to sql view:

select qty, price,
qty * price as total_without_tax ,
total_without_tax * .045 as TaxTotal,
total_without_tax * TaxTotal as GrandTotal
from ordersTable;

I need to use the aliases and not recalculate the values again in all of the fields. Thank you.

Regards
Tareq

It's Nice to Be Important But It's more Important to BE Nice

 

[ESquared]

select qty, price,
qty * price as total_without_tax ,
total_without_tax * .045 as TaxTotal,
total_without_tax * TaxTotal as GrandTotal
from ordersTable;

SELECT
   total_without_tax = amount,
   TaxTotal = amount * .045,
   GrandTotal = amount * 1.045
FROM (SELECT amount = qty * price from ordersTable) A

But these calculations are simple enough, that I can't imagine the speed benefit will be very much. If you are *really* insistent on not doing a calculation twice you can encapsulate the query twice into a derived table like I did above. (A is a random name I picked for the derived table in parentheses in front of it.)

SELECT
      total_without_tax = amount,
      TaxTotal = amount,
      GrandTotal = amount + tax
   FROM (
      SELECT
         amount,
         tax = amount * .045,
      FROM (SELECT amount = qty * price from ordersTable) A
   ) B

 

[Alibaba2003]

Thanks ESQuared ...
The actual queries are much more complex and involve decision statements as well as calculations. Some like this:

OVERHEAD = -- Here starts the overhead calculations
CASE WHEN SUB_JOURNAL <> 7 THEN
0
ELSE
CASE WHEN [FORM_PAYROLL1_DBASE].[TYPE_ID]=1 THEN
ISNULL(RATES_OVHD.[FT_ON],0)*([DEBIT]-[CREDIT])
ELSE

CASE WHEN [FORM_PAYROLL1_DBASE].[TYPE_ID]=4 THEN
ISNULL(RATES_OVHD.[FT_OFF],0)*([DEBIT]-[CREDIT])
ELSE


CASE WHEN [FORM_PAYROLL1_DBASE].[TYPE_ID]=2 THEN
ISNULL(RATES_OVHD.[PT_ON],0)*([DEBIT]-[CREDIT])

ELSE
ISNULL(RATES_OVHD.[PT_OFF],0)*([DEBIT]-[CREDIT])
END

END

END

END
-- Here Ends OverHead Calculations
,

RATES_GA.RATE*(([DEBIT]-[CREDIT])+ -- Here starts the overhead calculations
CASE WHEN SUB_JOURNAL <> 7 THEN
0
ELSE
CASE WHEN [FORM_PAYROLL1_DBASE].[TYPE_ID]=1 THEN
ISNULL(RATES_OVHD.[FT_ON],0)*([DEBIT]-[CREDIT])
ELSE

CASE WHEN [FORM_PAYROLL1_DBASE].[TYPE_ID]=4 THEN
ISNULL(RATES_OVHD.[FT_OFF],0)*([DEBIT]-[CREDIT])
ELSE


CASE WHEN [FORM_PAYROLL1_DBASE].[TYPE_ID]=2 THEN
ISNULL(RATES_OVHD.[PT_ON],0)*([DEBIT]-[CREDIT])

ELSE
ISNULL(RATES_OVHD.[PT_OFF],0)*([DEBIT]-[CREDIT])
END

END

END

END
-- Here Ends OverHead Calculations
) AS GA,

It's Nice to Be Important But It's more Important to BE Nice

 

[ESquared]

Well, I've demonstrated a principle that you can apply to your query. In any situation where you want to avoid repeating a calculation, make a derived table which does the calculation and join to it.

When you are done, you might try testing both methods and seeing if you really got a performance improvement.