Solved

using field in select statement

Posted on 2013-05-29
7
289 Views
Last Modified: 2013-05-29
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)

Open in new window

0
Comment
Question by:Shawn
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 39206080
Unfortunately, you'll have to duplicate your CASE statement.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 39206087
You cannot use it with your current query, you need some thing like this
SELECT * ,(p.ProductPrice-p.ProductPrice*VolumeDiscount)-((p.ProductPrice-p.ProductPrice*VolumeDiscount)*po.PaymentDiscount) AS UnitPrice

FROM (
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
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)
)A
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39206112
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.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Author Comment

by:Shawn
ID: 39206120
aneeshattingal
looks good but I'm getting the following error:
The multi-part identifier "p.ProductPrice" could not be bound.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39206132
Remove the p.'s from the first select column list.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39206135
Or change the alias A to p

WHERE     (cod.OrderID = 401001)
)A
 ------------------>
WHERE     (cod.OrderID = 401001)
)P

also remove the 'PO.'
0
 
LVL 1

Author Comment

by:Shawn
ID: 39206179
that worked niceley. just had to add po.PaymentDiscount in the other select query.
thank you!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now