Solved

Using column alias in where clause of select

Posted on 2008-10-13
4
2,572 Views
Last Modified: 2013-12-19
I have the following query(database is oracle):

SELECT TO_CHAR(TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS'),'DD.MM.YYYY:HH24:MI:SS') "createddate",
       TO_CHAR(createddate,'DD.MM.YYYY:HH24:MI:SS') "dateCreated",
       trunc(86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))-60*(trunc((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)) "Sec",
       trunc((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)-60*(trunc(((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)/60)) "Min",
       trunc(((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)/60)-24*(trunc((((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)/60)/24)) "Hrs",
       trunc((((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)/60)/24) "Days"
FROM table

which returns a column named "Days" with the difference in days between the created date in the table column and a date that i specified above.  I know that its not possible to use an alias in the where clause, so how can I get around it, basically i want to do something like this:

SELECT TO_CHAR(TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS'),'DD.MM.YYYY:HH24:MI:SS') "createddate",
       TO_CHAR(createddate,'DD.MM.YYYY:HH24:MI:SS') "dateCreated",
       trunc(86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))-60*(trunc((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)) "Sec",
       trunc((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)-60*(trunc(((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)/60)) "Min",
       trunc(((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)/60)-24*(trunc((((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)/60)/24)) "Hrs",
       trunc((((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)/60)/24) "Days"
FROM table where "Days" > 0
0
Comment
Question by:imrama
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22704303
you cannot use the column alias directly in the Where clause.
you have to either repeat the expression, or use a subquery ...
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22704327
Use an in-line view:

SELECT * FROM
(
SELECT TO_CHAR(TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS'),'DD.MM.YYYY:HH24:MI:SS') "createddate",
       TO_CHAR(createddate,'DD.MM.YYYY:HH24:MI:SS') "dateCreated",
       trunc(86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))-60*(trunc((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)) "Sec",
       trunc((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)-60*(trunc(((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)/60)) "Min",
       trunc(((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)/60)-24*(trunc((((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)/60)/24)) "Hrs",
       trunc((((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)/60)/24) "Days"
FROM table
) V

where "Days" > 0
0
 

Accepted Solution

by:
imrama earned 0 total points
ID: 22704360
i figured out just do this:

trunc((((86400*(createddate-TO_DATE('04.08.2008:10:34:24','DD.MM.YYYY:HH24:MI:SS')))/60)/60)/24) > 0
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22704507
Which is what Angelll meant by "repeating the expression".  In line view also works--it's a matter of style.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

821 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