CALCULATIONS ON ALIASES AFTER CASE 1

[Abracadabra]

I need to make a calculation on an alias after performing a Case statement. One work around I tried was to perform the calculations in another stored procedure, referencing this one. This worked on a view but not on another stored procedure. Can stored procedures query other stored procedures?

Incidentally it's worth mentioning that the end result will be become a table column. I realise this is regarded as poor practice, since it breaks the rules of normalisation. However, since the calculation of gross profit on a unit of sale can only end in one result, why not store this permanently for ease of field selection in later queries? Here is part of my code(written with help from Tek-Tip members).

CASE [sale or return]
WHEN 'R'
THEN - PI.[Movement value]
ELSE PI.[movement value]
END AS value,
CASE [sale or return]
WHEN 'R'
THEN - PI.[Cost of sales]
ELSE PI.[Cost of sales]
END AS cost,
round(value-cost,2) AS profit
FROM .......

Thanks very much in advance.

Bon weekend to you all.

Peter M

 

[tlbroadbent]

Hi Peter,

Thanks for the entry in my guest book.

You can't use the alias name in a SQL Server query. You can't select from a stored procedure. You can call SPs from other SPs and use output parameters. Another technique that may serve you well is to select from a subquery.

Select a.*, round(value-cost,2) AS profit
From (Select
CASE [sale or return]
WHEN 'R'
THEN - PI.[Movement value]
ELSE PI.[movement value]
END AS value,
CASE [sale or return]
WHEN 'R'
THEN - PI.[Cost of sales]
ELSE PI.[Cost of sales]
END AS cost,
FROM ....... ) As a


a.* selects all the values in the subquery select list and you can now reference value and cost for further calculations. The alias on the subquery (As a) is required. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin