Solved

correct syntax for greater than in WHEN clause

Posted on 2013-05-29
3
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
gt2847c earned 500 total points
ID: 39206012
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
ID: 39206031
perfect, thank you. one more question...posting now
0
 
LVL 1

Author Comment

by:Shawn
ID: 39206045
here's the new question Q_28142325
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
getting error Subquery returned more  than 1 value 6 23
SQL Job Hung 17 37
Upgrading to SQL Server 2015 Express 2 31
What Is an Error? 2 24
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

756 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