Query not recognize alias

Question

I have the following query, the alias it's called DELAY, works fine, but when I try to run the first query adding AND DELAY < 0, the alias is not recognized.

I tried to create the alias QUERY AS DELAY or QUERY AS "DELAY" but no success.

The strange thing is that the alias DELAY in the order by doesn't generate errors.

 SELECT ITK_TCK_ID, 
                   ITK_OPENED_DATE, 
                   TO_NUMBER(ROUND(ROUND(GET_HS(ITK_OPENED_DATE, 
                                                SYSDATE, 
                                                ITK_NORMAL_BEGIN, 
                                                ITK_NORMAL_END, 
                                                ITK_WEEKEND_BEGIN, 
                                                ITK_WEEKEND_END, 
                                                ITK_FREEDAY_BEGIN, 
                                                ITK_FREEDAY_END), 
                                         GET_HS(ITK_OPENED_DATE, 
                                                SYSDATE, 
                                                ITK_NORMAL_BEGIN, 
                                                ITK_NORMAL_END, 
                                                ITK_WEEKEND_BEGIN, 
                                                ITK_WEEKEND_END, 
                                                ITK_FREEDAY_BEGIN, 
                                                ITK_FREEDAY_END)) - 
                                   ITK_EXPIRATION_TIME, 
                                   2)) DELAY 
              FROM INSTANTIATED_TICKLERS 
             WHERE ITK_AR_SBP_ID = 'PEAJU_PP' -- P_WGR_ID 
             AND DELAY < 0 
             ORDER BY DELAY DESC;

Any suggestions?

Jmaurier · Answer 1 · 2015-07-07T19:13:19.800

That's because the WHERE executes before the SELECT. You could try calculating DELAY inside the WHERE

WHERE ITK_AR_SBP_ID = 'PEAJU_PP' -- P_WGR_ID 
AND TO_NUMBER(ROUND(ROUND(GET_HS(ITK_OPENED_DATE, 
             SYSDATE, 
             ITK_NORMAL_BEGIN, 
             ITK_NORMAL_END, 
             ITK_WEEKEND_BEGIN, 
             ITK_WEEKEND_END, 
             ITK_FREEDAY_BEGIN, 
             ITK_FREEDAY_END), 
        GET_HS(ITK_OPENED_DATE, 
             SYSDATE, 
             ITK_NORMAL_BEGIN, 
             ITK_NORMAL_END, 
             ITK_WEEKEND_BEGIN, 
             ITK_WEEKEND_END, 
             ITK_FREEDAY_BEGIN, 
             ITK_FREEDAY_END)) - 
        ITK_EXPIRATION_TIME, 
      2) < 0

score 1 · Accepted Answer · answered Jul 07 '15 at 19:14

You can not use column aliases in the WHERE clause at the same level where the alias was defined.

http://docs.oracle.com/cd/E11882_01/server.112/e10592/statements_10002.htm#SQLRF55280

c_alias

Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.

Use an inline view:

 SELECT * FROM (
     SELECT ITK_TCK_ID, 
                       ITK_OPENED_DATE, 
                       TO_NUMBER(ROUND(ROUND(GET_HS(ITK_OPENED_DATE, 
                                                    SYSDATE, 
                                                    ITK_NORMAL_BEGIN, 
                                                    ITK_NORMAL_END, 
                                                    ITK_WEEKEND_BEGIN, 
                                                    ITK_WEEKEND_END, 
                                                    ITK_FREEDAY_BEGIN, 
                                                    ITK_FREEDAY_END), 
                                             GET_HS(ITK_OPENED_DATE, 
                                                    SYSDATE, 
                                                    ITK_NORMAL_BEGIN, 
                                                    ITK_NORMAL_END, 
                                                    ITK_WEEKEND_BEGIN, 
                                                    ITK_WEEKEND_END, 
                                                    ITK_FREEDAY_BEGIN, 
                                                    ITK_FREEDAY_END)) - 
                                       ITK_EXPIRATION_TIME, 
                                       2)) DELAY 
                  FROM INSTANTIATED_TICKLERS 
                 WHERE ITK_AR_SBP_ID = 'PEAJU_PP' -- P_WGR_ID 
  )          
  WHERE DELAY < 0 
  ORDER BY DELAY DESC;

Query not recognize alias

2 Answers2