asked on

Nested CASE T-SQL statements

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

dan_neal

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

ASKER CERTIFIED SOLUTION

dan_neal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

reb73

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

Guy Hengel [angelIII / a3]

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