Solved

correct syntax for greater than in WHEN clause

Posted on 2013-05-29
3
235 Views
Last Modified: 2013-05-29
Can't get this to work. Any ideas where my syntax is wrong here?

Case cod.Quantity WHEN < 5 THEN 0 WHEN < 10 THEN 0.05 ELSE 0.1 END AS VolumeDiscount

Open in new window


full statement
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 cod.Quantity WHEN < 5 THEN 0 WHEN < 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)

Open in new window

0
Comment
Question by:Shawn
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
gt2847c earned 500 total points
Comment Utility
Try it as:
CASE WHEN cod.Quantity < 5 THEN 0
     WHEN cod.Quantity < 10 THEN 0.05
     ELSE 0.1 END AS [VolumeDiscount]

Open in new window

0
 
LVL 1

Author Comment

by:Shawn
Comment Utility
perfect, thank you. one more question...posting now
0
 
LVL 1

Author Comment

by:Shawn
Comment Utility
here's the new question Q_28142325
0

Featured Post

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

Join & Write a Comment

Suggested Solutions

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 …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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

10 Experts available now in Live!

Get 1:1 Help Now