• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

correct syntax for greater than in WHEN clause

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
Shawn
Asked:
Shawn
  • 2
1 Solution
 
gt2847cCommented:
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
 
ShawnAuthor Commented:
perfect, thank you. one more question...posting now
0
 
ShawnAuthor Commented:
here's the new question Q_28142325
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now