Trouble Aliasing a CASE statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trouble Aliasing a CASE statement

Author

Topic

kohlhaas77
Starting Member

25 Posts

Posted - 2008-07-21 : 10:25:21

I get an "Invalid column name 'SETVALUE' error witht eh following syntax...

SELECT S1.PROV_ID, S1.TAX_ID_NBR, S1.ACPT_PTNT_CD, S1.CANC_DT, S1.CONTR_ARNG_CD, S1.EFF_DT, S1.FEE_SCHED_NBR, S1.IPA_NBR,
CASE WHEN S1.IPA_NBR IN (1,8,11,37) THEN 'SET1'
WHEN S1.IPA_NBR IN (2,10,230,500) THEN 'SET2'
ELSE 'NULLSET'
END AS SETVALUE,
S1.MKT_NBR, S1.PAY_METH_CD, S1.PRDCT_OFR_ID, S1.PROV_CONTR_ROLE_CD, S1.CONTR_ID, S1.PROV_TYP_CD, S1.ORG_TYP_CD, S1.LST_UPDT_DT,
S1.CREATE_DATE, S1.MASTER_ID, S1.SPECIALTY_CODE, S1.SPECIALTY_PRIMARY_IND, S1.SPECIALTY_DESC
--INTO Table2
FROM Table1
WHERE SETVALUE <> 'NULLSET'

Any help would be very much appreciated!

Dan Kohlhaas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 10:26:52

[code]
SELECT *
INTO Table2
FROM
(
SELECT S1.PROV_ID, S1.TAX_ID_NBR, S1.ACPT_PTNT_CD, S1.CANC_DT, S1.CONTR_ARNG_CD, S1.EFF_DT, S1.FEE_SCHED_NBR, S1.IPA_NBR,
CASE WHEN S1.IPA_NBR IN (1,8,11,37) THEN 'SET1'
WHEN S1.IPA_NBR IN (2,10,230,500) THEN 'SET2'
ELSE 'NULLSET'
END AS SETVALUE,
S1.MKT_NBR, S1.PAY_METH_CD, S1.PRDCT_OFR_ID, S1.PROV_CONTR_ROLE_CD, S1.CONTR_ID, S1.PROV_TYP_CD, S1.ORG_TYP_CD, S1.LST_UPDT_DT,
S1.CREATE_DATE, S1.MASTER_ID, S1.SPECIALTY_CODE, S1.SPECIALTY_PRIMARY_IND, S1.SPECIALTY_DESC
FROM Table1)t
WHERE t.SETVALUE <> 'NULLSET'[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-21 : 10:27:03

SELECT * FROM
(
SELECT S1.PROV_ID, S1.TAX_ID_NBR, S1.ACPT_PTNT_CD, S1.CANC_DT, S1.CONTR_ARNG_CD, S1.EFF_DT, S1.FEE_SCHED_NBR, S1.IPA_NBR,
CASE WHEN S1.IPA_NBR IN (1,8,11,37) THEN 'SET1'
WHEN S1.IPA_NBR IN (2,10,230,500) THEN 'SET2'
ELSE 'NULLSET'
END AS SETVALUE,
S1.MKT_NBR, S1.PAY_METH_CD, S1.PRDCT_OFR_ID, S1.PROV_CONTR_ROLE_CD, S1.CONTR_ID, S1.PROV_TYP_CD, S1.ORG_TYP_CD, S1.LST_UPDT_DT,
S1.CREATE_DATE, S1.MASTER_ID, S1.SPECIALTY_CODE, S1.SPECIALTY_PRIMARY_IND, S1.SPECIALTY_DESC
--INTO Table2
FROM Table1
) AS T
WHERE SETVALUE <> 'NULLSET'

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-21 : 10:27:27