Mega Code Archive
Doing a calculation in a Searched CASE
<![CDATA[
5>
6>
7> CREATE TABLE OrderDetails (
8> OrderID int NOT NULL ,
9> ProductID int NOT NULL ,
10> UnitPrice money NOT NULL DEFAULT (0),
11> Quantity smallint NOT NULL DEFAULT (1),
12> Discount real NOT NULL DEFAULT (0)
13> )
14> GO
1> INSERT OrderDetails VALUES(10248,11,14,12,0)
2> INSERT OrderDetails VALUES(10248,42,9.8,10,0)
3> INSERT OrderDetails VALUES(10248,72,34.8,5,0)
4> INSERT OrderDetails VALUES(10249,14,18.6,9,0)
5> INSERT OrderDetails VALUES(10249,51,42.4,40,0)
6> INSERT OrderDetails VALUES(10250,41,7.7,10,0)
7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15)
8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15)
9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05)
10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05)
11> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SELECT TOP 10 OrderID % 10 AS "Last Digit",
4> ProductID,
5> "How Close?" = CASE
6> WHEN (OrderID % 10) < 3 THEN 'Ends With Less Than Three'
7> WHEN ProductID = 6 THEN 'ProductID is 6'
8> WHEN ABS(OrderID % 10 - ProductID) <= 1 THEN 'Within 1'
9> ELSE 'More Than One Apart'
10> END
11> FROM OrderDetails
12> WHERE ProductID < 10
13> ORDER BY OrderID DESC
14> GO
Last Digit ProductID How Close?
----------- ----------- -------------------------
(0 rows affected)
1>
2> drop table OrderDetails;
3> GO
]]>