Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Nested CASE T-SQL statements

Posted on 2009-04-15
4
Medium Priority
?
615 Views
Last Modified: 2012-05-06
Hi all, I'm trying to convert some Access queries to T-SQL and I was wondering if it is at all possible to replace references to expressions in the Access query with a nested CASE statement in SQL.

See below for the code and a regular ole "IF" statement where I need to somehow nest case statements.



SELECT a.TRXSORCE AS PO_No,
b.DOCNUMBR AS Vendor_Doc,
b.TRXLOCTN AS Port,
b.ITEMNMBR AS ItemID,
b.TRXQTY AS Ship_Qty,
CASE 
	WHEN b.TRXQTY >= 0 THEN b.EXTDCOST
	ELSE b.EXTDCOST * -1
END AS Inv_Total, /* EXPR2 */
NULL AS InvoiceNo,
b.DOCDATE AS ReceiptDate,
a.POSTEDDT AS PostedDate,
0 AS Match_Qty,
CASE c.CalcValue
	WHEN 'Y' THEN b.TRXQTY * d.Price
	ELSE 0
END AS Extended_Cost, /* EXPR3 */
/* IF c.CalcValue = 'Y' Then EXPR2-EXPR3 ELSE 0 */ AS Landed_Cost,
0 AS Surcharge_Total
FROM GREAT_PLAINS.ANSAC.dbo.IV30100 a
JOIN GREAT_PLAINS.ANSAC.dbo.IV30300 b
ON a.TRXSORCE = b.TRXSORCE
JOIN AA_Item_Convert c
ON b.ITEMNMBR = c.Item
JOIN tbl_Price d
ON SUBSTRING(CONVERT(NVARCHAR(12), b.DOCDATE, 107), 1, 3) + '-' + SUBSTRING(CONVERT(NVARCHAR(12), b.DOCDATE, 107), 9, 4) = d.[Month]
WHERE a.TRXSORCE LIKE 'IVA%'
AND b.DOCDATE BETWEEN '03/01/2009' AND '03/31/2009'

Open in new window

0
Comment
Question by:HarpuaFSB
  • 2
4 Comments
 
LVL 9

Expert Comment

by:dan_neal
ID: 24150987
Give this a shot:

SELECT a.TRXSORCE AS PO_No,
b.DOCNUMBR AS Vendor_Doc,
b.TRXLOCTN AS Port,
b.ITEMNMBR AS ItemID,
b.TRXQTY AS Ship_Qty,
CASE 
	WHEN b.TRXQTY >= 0 THEN b.EXTDCOST
	ELSE b.EXTDCOST * -1
END AS Inv_Total, /* EXPR2 */
NULL AS InvoiceNo,
b.DOCDATE AS ReceiptDate,
a.POSTEDDT AS PostedDate,
0 AS Match_Qty,
CASE c.CalcValue
	WHEN 'Y' THEN b.TRXQTY * d.Price
	ELSE 0
END AS Extended_Cost, /* EXPR3 */
/* IF c.CalcValue = 'Y' Then EXPR2-EXPR3 ELSE 0 */ 
/*New case statement here*/
case when c.calc = 'Y' then case when b.trxqty >= 0 then b.extdcost else b.extdcost * -1 end - (b.trxqty * d.price) else 0 end AS Landed_Cost,
/*End new case statement*/
0 AS Surcharge_Total
FROM GREAT_PLAINS.ANSAC.dbo.IV30100 a
JOIN GREAT_PLAINS.ANSAC.dbo.IV30300 b
ON a.TRXSORCE = b.TRXSORCE
JOIN AA_Item_Convert c
ON b.ITEMNMBR = c.Item
JOIN tbl_Price d
ON SUBSTRING(CONVERT(NVARCHAR(12), b.DOCDATE, 107), 1, 3) + '-' + SUBSTRING(CONVERT(NVARCHAR(12), b.DOCDATE, 107), 9, 4) = d.[Month]
WHERE a.TRXSORCE LIKE 'IVA%'
AND b.DOCDATE BETWEEN '03/01/2009' AND '03/31/2009'

Open in new window

0
 
LVL 9

Accepted Solution

by:
dan_neal earned 2000 total points
ID: 24150994
Typo:
SELECT a.TRXSORCE AS PO_No,
b.DOCNUMBR AS Vendor_Doc,
b.TRXLOCTN AS Port,
b.ITEMNMBR AS ItemID,
b.TRXQTY AS Ship_Qty,
CASE 
	WHEN b.TRXQTY >= 0 THEN b.EXTDCOST
	ELSE b.EXTDCOST * -1
END AS Inv_Total, /* EXPR2 */
NULL AS InvoiceNo,
b.DOCDATE AS ReceiptDate,
a.POSTEDDT AS PostedDate,
0 AS Match_Qty,
CASE c.CalcValue
	WHEN 'Y' THEN b.TRXQTY * d.Price
	ELSE 0
END AS Extended_Cost, /* EXPR3 */
/* IF c.CalcValue = 'Y' Then EXPR2-EXPR3 ELSE 0 */ 
/*New case statement here*/
case when c.calcvalue = 'Y' then case when b.trxqty >= 0 then b.extdcost else b.extdcost * -1 end - (b.trxqty * d.price) else 0 end AS Landed_Cost,
/*End new case statement*/
0 AS Surcharge_Total
FROM GREAT_PLAINS.ANSAC.dbo.IV30100 a
JOIN GREAT_PLAINS.ANSAC.dbo.IV30300 b
ON a.TRXSORCE = b.TRXSORCE
JOIN AA_Item_Convert c
ON b.ITEMNMBR = c.Item
JOIN tbl_Price d
ON SUBSTRING(CONVERT(NVARCHAR(12), b.DOCDATE, 107), 1, 3) + '-' + SUBSTRING(CONVERT(NVARCHAR(12), b.DOCDATE, 107), 9, 4) = d.[Month]
WHERE a.TRXSORCE LIKE 'IVA%'
AND b.DOCDATE BETWEEN '03/01/2009' AND '03/31/2009'

Open in new window

0
 
LVL 25

Expert Comment

by:reb73
ID: 24150999
Another alternative is to use your original query as a subquery and refer to the derived calculations by their aliased names -
SELECT *, CASE WHEN Extended_Cost = 0 THEN 0 ELSE Inv_Total-Extended_Cost END  AS Landed_Cost
FROM
	(SELECT
		a.TRXSORCE AS PO_No,
		b.DOCNUMBR AS Vendor_Doc,
		b.TRXLOCTN AS Port,
		b.ITEMNMBR AS ItemID,
		b.TRXQTY AS Ship_Qty,
		CASE 
		        WHEN b.TRXQTY >= 0 THEN b.EXTDCOST
		        ELSE b.EXTDCOST * -1
		END AS Inv_Total, /* EXPR2 */
		NULL AS InvoiceNo,
		b.DOCDATE AS ReceiptDate,
		a.POSTEDDT AS PostedDate,
		0 AS Match_Qty,
		CASE c.CalcValue
		        WHEN 'Y' THEN b.TRXQTY * d.Price
		        ELSE 0
		END AS Extended_Cost, 	/* EXPR3 */
		0 AS Surcharge_Total
	FROM GREAT_PLAINS.ANSAC.dbo.IV30100 a
	JOIN GREAT_PLAINS.ANSAC.dbo.IV30300 b ON a.TRXSORCE = b.TRXSORCE
	JOIN AA_Item_Convert c ON b.ITEMNMBR = c.Item
	JOIN tbl_Price d ON SUBSTRING(CONVERT(NVARCHAR(12), b.DOCDATE, 107), 1, 3) + '-' + SUBSTRING(CONVERT(NVARCHAR(12), b.DOCDATE, 107), 9, 4) = d.[Month]
	WHERE a.TRXSORCE LIKE 'IVA%'
	AND b.DOCDATE BETWEEN '03/01/2009' AND '03/31/2009'
	) Tmp

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24151006
you cannot use the expression alias directly.
so, you have 2 options:
* reuse the expression itself
* use a subquery like this:
select expr1, expr2, expr1-expr2
  from  (select a+b expr1, a-b expr2 from yourtable ) sq

Open in new window

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

810 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