[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using column alias in where clause of select

Posted on 2008-10-13
4
Medium Priority
?
2,606 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

834 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