Solved

Reference Alias in case statment

Posted on 2007-11-27
3
5,219 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 9

Accepted Solution

by:
joebednarz earned 125 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

697 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