Databases
StackOverflow2013
Postsdbo
POSQL.. Alias name in where condition
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL.. Alias name in where condition
    text
    copied!<blockquote> <p><strong>Possible Duplicate:</strong><br> <a href="https://stackoverflow.com/questions/356675/using-an-alias-in-a-where-clause">Using an Alias in a WHERE clause</a> </p> </blockquote> <p>Please let me know why i'm not able to use the alias names in where condition for the query below.. </p> <pre><code>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 &gt;= TO_DATE('01082013', 'MMDDYYYY') AND UH.ENTRY_DATE &lt;= 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 &gt;= TO_DATE('01082013', 'MMDDYYYY') AND UH.ENTRY_DATE &lt;= TO_DATE('01092013', 'MMDDYYYY') ORDER BY U.USER_NAME, UH.ENTRY_DATE) ) as innerTable WHERE Status &lt;&gt;'LOGGED_OUT' ORDER BY 1,3 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload