using a created column name in subsequent case statement

[swhitten]

I am writing a select statement to use in a view. The first column of data I need is retrieved with the first case statement below; I call that column TxnCode. Then I need to write data to a second column that depends on the TxnCode returned in the first column. The script below tells me 'invalid column mame 'TxnCode''. It is not recognizing the alias from the first case statement.

Is there an easy way to re-write this so I can use the TxnCode in the second case statement? Thanks.


SELECT
CASE
WHEN doctype = 6 THEN 'SPPC'
WHEN doctype = 4 THEN 'RPPC'
WHEN doctype = 1 AND trxqty < 1 THEN 'ANPC'
WHEN doctype = 1 AND trxqty > 1 THEN 'APPC'
ELSE 'HELP' END AS TxnCode,

CASE TxnCode
WHEN 'RPPC' THEN 'I'
WHEN 'APPC' THEN 'I'
WHEN 'RNPC' then ''
WHEN 'SPPC' THEN ''
WHEN 'ANPC' THEN ''
ELSE 'HELP' END as NewColumn

FROM dbo.IV30300

 

[r937]

SELECT TxnCode
     , CASE WHEN TxnCode IN ('RPPC','APPC') 
            THEN 'I' 
            WHEN TxnCode IN ('RNPC','SPPC','ANPC') 
            THEN '' 
            ELSE 'HELP' END as NewColumn
  FROM ( SELECT CASE WHEN doctype = 6                
                     THEN 'SPPC'                     
                     WHEN doctype = 4                
                     THEN 'RPPC'                     
                     WHEN doctype = 1 AND trxqty < 1 
                     THEN 'ANPC'                     
                     WHEN doctype = 1 AND trxqty > 1 
                     THEN 'APPC'                     
                     ELSE 'HELP' END AS TxnCode 
           FROM dbo.IV30300 ) AS t

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[swhitten]

Thanks! I will test tomorrow.

 

[swhitten]

Okay, this is getting me closer. Now I need to add other fields from IV30300 as well as additional created columns to my select statement. Not sure where to put these additional items in the new select statement. I need to add fields like:

'12345' AS PartnerID,
CONVERT(varchar, GETDATE(), 112) + 'T000000' AS EffDate,
ITEMNMBR AS ProductNum,
CASE WHEN trxqty > 0 THEN TrxQty ELSE (TrxQty * - 1) END AS TxnQty

Where do I put those in the revised select statement above? Thanks again!

 

[r937]

add them to the inner query, and add the aliases to the outer query

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon