Solved

Using column alias in where clause of select

Posted on 2008-10-13
4
2,574 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
[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
  • 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

Technology Partners: 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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 63
Duplicated data in GROUP_CONCAT 2 48
Need to replicate a Log table 4 38
How do i delete the last node in an xml in T-SQL 7 19
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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…

738 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