Solved

Using column alias in where clause of select

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

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

695 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