SQL.. Alias name in where condition

Question

Possible Duplicate:
Using an Alias in a WHERE clause

Please let me know why i'm not able to use the alias names in where condition for the query below..

SELECT * FROM (        
    SELECT USER_NAME AS "User ID",
         OLD_VALUE_STRING as "Status",
         NVL(lag(ENTRY_DATE) over (partition by user_name order by ENTRY_DATE), trunc(Sysdate-1)) as "Begin", 
           ENTRY_DATE as "End"
         FROM (SELECT U.USER_NAME,
                 U.FULL_NAME,
                 UH.OLD_VALUE_STRING,
                 UH.NEW_VALUE_STRING,
                 UH.ENTRY_DATE,
                 US.IS_WORKING
            FROM VIW.USER_HISTORY UH, VIW.USERS U, VIW.USER_STATUS US
           WHERE U.ID = UH.USER_ID
             AND UH.EVENT_ID = VIW.EVENT_PKG.GET_ID('UPD_USR_STATUS')
             AND UH.NEW_VALUE_STRING = US.ABBREVIATION
             AND UH.ENTRY_DATE >= TO_DATE('01082013', 'MMDDYYYY')
             AND UH.ENTRY_DATE <= TO_DATE('01092013', 'MMDDYYYY')
           ORDER BY U.USER_NAME, UH.ENTRY_DATE) 

    UNION        

    SELECT USER_NAME AS "User ID",
         NEW_VALUE_STRING as "Status",
         ENTRY_DATE AS "Begin", 
           lead(ENTRY_DATE) over (partition by user_name order by ENTRY_DATE) as "End"
           FROM (SELECT U.USER_NAME,
                 U.FULL_NAME,
                 UH.OLD_VALUE_STRING,
                 UH.NEW_VALUE_STRING,
                 UH.ENTRY_DATE,
                 US.IS_WORKING
            FROM VIW.USER_HISTORY UH, VIW.USERS U, VIW.USER_STATUS US
           WHERE U.ID = UH.USER_ID
             AND UH.EVENT_ID = VIW.EVENT_PKG.GET_ID('UPD_USR_STATUS')
             AND UH.NEW_VALUE_STRING = US.ABBREVIATION
             AND UH.ENTRY_DATE >= TO_DATE('01082013', 'MMDDYYYY')
             AND UH.ENTRY_DATE <= TO_DATE('01092013', 'MMDDYYYY')
           ORDER BY U.USER_NAME, UH.ENTRY_DATE)               
)  as innerTable
           WHERE Status <>'LOGGED_OUT'     
           ORDER BY 1,3

Because that's not something that SQL allows you to do... Read the documentation. — Jordan Kaye, Jan 09 '13 at 20:06
+1@JordanKaye You may use a `case`... here some other [relevant posts](http://stackoverflow.com/questions/356675/using-an-alias-in-a-where-clause) — bonCodigo, Jan 09 '13 at 20:09
Why can't you use `innerTable.NEW_VALUE_STRING<>'LOGGED_OUT' `? (Or did I just read it wrong?) — Popnoodles, Jan 09 '13 at 20:10
You can use table alias but you can't use column alias...Also try to move query into an inline view `without the where-clause predicate`, and then add the where-clause predicate, using the alias, in the outer query... — bonCodigo, Jan 09 '13 at 20:11
BTW, you should consider using JOINS in your FROM statement. — sgeddes, Jan 09 '13 at 20:12
@popnoodles: I think the user is referring to the `STATUS` in the 2nd to last line, which is an alias coming from the SELECT. — DWright, Jan 09 '13 at 20:21
Yes, which they've create from `NEW_VALUE_STRING` from the table with alias `innerTable`. i.e. `Status` in the where clause == `innerTable.NEW_VALUE_STRING` — Popnoodles, Jan 09 '13 at 20:24
user1430989 could you update the question to clarify that you are talking about the STATUS alias (if that's true)? This will help you question (if true). — DWright, Jan 09 '13 at 20:24
Implicit joins are a SQL antipattern. Why are you still using them? — HLGEM, Jan 09 '13 at 20:39

DWright · Answer 1 · 2013-01-09T20:19:00.083

3

All the stuff in the FROMs and JOINS and the WHERE is used to produce all the stuff for the SELECT. So you can't uses aliases from the SELECT in the WHERE, because the WHERE is logically prior. The rows presented to the SELECT come after the WHERE was already applied.

So Status isn't known to the WHERE at all.

edited Jan 09 '13 at 20:19

answered Jan 09 '13 at 20:11

DWright

9,258
4
36
53

score 1 · Answer 2 · edited Jan 09 '13 at 20:37

I found the solution myself. Thank your for your time though.

SELECT * FROM (        
    SELECT USER_NAME AS "User ID",
         OLD_VALUE_STRING as "STATUS",
         NVL(lag(ENTRY_DATE) over (partition by user_name order by ENTRY_DATE), trunc(Sysdate-1)) as "Begin", 
           ENTRY_DATE as "End"
         FROM (SELECT U.USER_NAME,
                 U.FULL_NAME,
                 UH.OLD_VALUE_STRING,
                 UH.NEW_VALUE_STRING,
                 UH.ENTRY_DATE,
                 US.IS_WORKING
            FROM VIW.USER_HISTORY UH, VIW.USERS U, VIW.USER_STATUS US
           WHERE U.ID = UH.USER_ID
             AND UH.EVENT_ID = VIW.EVENT_PKG.GET_ID('UPD_USR_STATUS')
             AND UH.NEW_VALUE_STRING = US.ABBREVIATION
             AND UH.ENTRY_DATE >= TO_DATE('01082013', 'MMDDYYYY')
             AND UH.ENTRY_DATE <= TO_DATE('01092013', 'MMDDYYYY')
           ORDER BY U.USER_NAME, UH.ENTRY_DATE) 

    UNION        

    SELECT USER_NAME AS "User ID",
         NEW_VALUE_STRING as "STATUS",
         ENTRY_DATE AS "Begin", 
           lead(ENTRY_DATE) over (partition by user_name order by ENTRY_DATE) as "End"
           FROM (SELECT U.USER_NAME,
                 U.FULL_NAME,
                 UH.OLD_VALUE_STRING,
                 UH.NEW_VALUE_STRING,
                 UH.ENTRY_DATE,
                 US.IS_WORKING
            FROM VIW.USER_HISTORY UH, VIW.USERS U, VIW.USER_STATUS US
           WHERE U.ID = UH.USER_ID
             AND UH.EVENT_ID = VIW.EVENT_PKG.GET_ID('UPD_USR_STATUS')
             AND UH.NEW_VALUE_STRING = US.ABBREVIATION
             AND UH.ENTRY_DATE >= TO_DATE('01082013', 'MMDDYYYY')
             AND UH.ENTRY_DATE <= TO_DATE('01092013', 'MMDDYYYY')
           ORDER BY U.USER_NAME, UH.ENTRY_DATE)               
) 
           WHERE STATUS <>'LOGGED_OUT'     
           ORDER BY 1,3

SQL.. Alias name in where condition

2 Answers2