?
Solved

Reference Alias in case statment

Posted on 2007-11-27
3
Medium Priority
?
5,247 Views
Last Modified: 2010-04-21
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
           
0
Comment
Question by:lfolku
3 Comments
 
LVL 9

Accepted Solution

by:
joebednarz earned 500 total points
ID: 20359159
Something to try:


SELECT SCPL, EIN, SOI_MAJ_CD, SOI_DIV_CD, NET_OTHR_INCM,
       CASE
          WHEN 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) S_NET_INCM,
               NVL (NET_OTHR_INCM, 0) NET_OTHR_INCM
          FROM ENTITY_ADDITIONAL) A
       JOIN
       CONTROL C USING (SCPL, EDIT_1_2)

Open in new window

0
 
LVL 48

Expert Comment

by:schwertner
ID: 20359235
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
0
 

Author Closing Comment

by:lfolku
ID: 31411214
It worked.....thanks!!!
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

601 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