asked on

Reference Alias in case statment

Hi Experts,

I'm wondering if you can reference a alias column (columns added together) within a case statement:

The aslias columns is S_NET_INCM.

Select SCPL, EIN, SOI_MAJ_CD, SOI_DIV_CD,
NVL(NET_INCM, 0) + NVL(S_NET_INCM_OTHR_RNTL, 0) + NVL(S_NET_LTCG_LSS, 0) +
NVL(S_NET_STCG_LSS, 0) AS "S_NET_INCM" ,
NVL(NET_OTHR_INCM, 0),
CASE
WHEN "S_NET_INCM: < 0 THEN
0
ELSE
S_NET_INCM_POS
END "S_NET_INCM_POS"
FROM ENTITY_ADDITIONAL JOIN CONTROL C USING (SCPL, EDIT_1_2)

Thanks

ASKER CERTIFIED SOLUTION

joebednarz

membership

This solution is only available to members.

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

Start Free Trial

schwertner

A possible workaround:

SELECT a.SCPL, a.EIN ....,
CASE
WHEN "a.S_NET_INCM: < 0 THEN
0
ELSE
S_NET_INCM_POS
END "S_NET_INCM_POS"
FROM
(Select SCPL, EIN, SOI_MAJ_CD, SOI_DIV_CD,
NVL(NET_INCM, 0) + NVL(S_NET_INCM_OTHR_RNTL, 0) + NVL(S_NET_LTCG_LSS, 0) +
NVL(S_NET_STCG_LSS, 0) AS "S_NET_INCM" ,
NVL(NET_OTHR_INCM, 0) AS "NET_OTHR_INCM",
FROM ENTITY_ADDITIONAL JOIN CONTROL C USING (SCPL, EDIT_1_2)) a

lfolku

ASKER

It worked.....thanks!!!