Shawn
asked on
using field in select statement
in the select statement below I set up a field AS VolumeDiscount. I would like to use VolumeDiscount in the same select statement right after. How would I do this?
SELECT cod.OrderDetailsID, cod.OrderID, cod.DeliveryDateRequest, cod.GrowthStageID, cod.StandingOrder, cod.Comments, cod.Quantity, cod.ClientSpecialDiscount,
cod.ResellerSpecialDiscount, cod.ProductID,
CASE cod.GrowthStageID WHEN 3 THEN p.ProductPrice - 2 WHEN 2 THEN p.ProductPrice - 1 ELSE p.ProductPrice END AS ProductPrice
,CASE WHEN cod.Quantity < 5 THEN 0 WHEN cod.Quantity < 10 THEN 0.05 ELSE 0.1 END AS VolumeDiscount
,(p.ProductPrice-p.ProductPrice*VolumeDiscount)-((p.ProductPrice-p.ProductPrice*VolumeDiscount)*po.PaymentDiscount) AS UnitPrice
FROM WW.ClientOrderDetails AS cod INNER JOIN
WW.Products AS p ON cod.ProductID = p.ProductID INNER JOIN
WW.TaxCategories AS tc ON p.TaxCategoryID = tc.TaxCategoryID INNER JOIN
WW.ClientOrders AS co ON cod.OrderID = co.OrderID INNER JOIN
WW.PaymentOptions AS po ON co.PaymentOptionID = po.PaymentOptionID
WHERE (cod.OrderID = 401001)
Unfortunately, you'll have to duplicate your CASE statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can also define the inner select as a CTE, or a view, or like above, it remains the same. Only by using some intermediate table you can reuse results of expressions.
ASKER
aneeshattingal
looks good but I'm getting the following error:
The multi-part identifier "p.ProductPrice" could not be bound.
looks good but I'm getting the following error:
The multi-part identifier "p.ProductPrice" could not be bound.
Remove the p.'s from the first select column list.
Or change the alias A to p
WHERE (cod.OrderID = 401001)
)A
------------------>
WHERE (cod.OrderID = 401001)
)P
also remove the 'PO.'
WHERE (cod.OrderID = 401001)
)A
------------------>
WHERE (cod.OrderID = 401001)
)P
also remove the 'PO.'
ASKER
that worked niceley. just had to add po.PaymentDiscount in the other select query.
thank you!
thank you!