Can I use Calced field in same query?

Question

Post reply

Can I use Calced field in same query?

turkey-500673

Mr or Mrs. 500

Points: 516
More actions
January 15, 2009 at 9:57 am

#196140

I have a query that has a case statement
CASE when ((100 * inventory_supplier.cost * 1.1) / (100.00000001 - case l.unit_price
WHEN 0 THEN
1 --avoid divide by 0 error
ELSE
(l.unit_price - l.commission_cost) / l.unit_price * 100
END ) ) / ( (i.purchase_pricing_unit_size / l.pricing_unit_size) ) > l.unit_price
then
((100 * inventory_supplier.cost * 1.1) / (100.00000001 -
CASE l.unit_price
WHEN 0 THEN
1 --avoid divide by 0 error
ELSE
(l.unit_price - l.commission_cost) / l.unit_price * 100
END ) ) / ( (i.purchase_pricing_unit_size / l.pricing_unit_size) )
else
l.unit_price
end New_Sell_Price2
I want to use the result from New_Sell_price2
as (New_Sell_price2 - l.commision_cost) / New_Sell_price2
but I don't know to grab New_Sell_price2
It's driving me stark raving mad!
Can anyone help?
Emil

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it's not obvious, but what you want to do is stick your query into a subquery, and use the where statement outside:

[font="Courier New"]SELECT MySubQueryAlias.*,

(MySubQueryAlias.New_Sell_price2

- MySubQueryAlias.commision_cost)

/ MySubQueryAlias.New_Sell_price2 AS MYRESULTS

FROM (your big query here[/b]) MySubQueryAlias

WHERE MySubQueryAlias.New_Sell_price2 > 0

[/font]

turkey (1/15/2009)
I have a query that has a case statement
CASE when ((100 * inventory_supplier.cost * 1.1) / (100.00000001 - case l.unit_price
WHEN 0 THEN
1 --avoid divide by 0 error
ELSE
(l.unit_price - l.commission_cost) / l.unit_price * 100
END ) ) / ( (i.purchase_pricing_unit_size / l.pricing_unit_size) ) > l.unit_price
then
((100 * inventory_supplier.cost * 1.1) / (100.00000001 -
CASE l.unit_price
WHEN 0 THEN
1 --avoid divide by 0 error
ELSE
(l.unit_price - l.commission_cost) / l.unit_price * 100
END ) ) / ( (i.purchase_pricing_unit_size / l.pricing_unit_size) )
else
l.unit_price
end New_Sell_Price2
I want to use the result from New_Sell_price2
as (New_Sell_price2 - l.commision_cost) / New_Sell_price2
but I don't know to grab New_Sell_price2
It's driving me stark raving mad!
Can anyone help?
Emil

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Charles Kincaid SSChampion Points: 13593 More actions · Answer 2

If you are on SQL 2005 or later look at putting your calculation into a CTE (Common Table Expression). See books on line or Google for details.

ATBCharles Kincaid

turkey-500673 Mr or Mrs. 500 Points: 516 More actions · Answer 3

Not working for me...(big query here) is that the query name only or do I stick the whole query there?

I'm using sql 2000 btw.

Lowell SSC Guru Points: 323518 More actions · Answer 4

yeah replace"big query here" with your full SELECT...CASE...WHERE... statement that you are using...you had pasted just the cASE section as your example.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

turkey-500673 Mr or Mrs. 500 Points: 516 More actions · Answer 5

now getting"invalid column commission cost"

any answers?

( I told you this would drive you mad!)

Thanks for any help

Emil

Lowell SSC Guru Points: 323518 More actions · Answer 6

paste your real full Select statement here...it looks like you have a column name with a space in it, so it needs to be wrapped in brackets, ie [commision cost]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!