How to use a calculated column in where condition?

Question

How to use a calculated column in the where condition in Oracle 9i?

I want to use something like

select decode (:pValue,1,
               select sysdate from dual,
               select activation_date from account where AcNo = 1234) as calDate
where caldate between startDate and endDate;

score 9 · Accepted Answer · edited Mar 11 '10 at 02:37

9

You can use an in-line view:

select calcdate from
(
select startDate, endDate,
       decode (:pValue,1,
               select sysdate from dual,
               select activation_date from account where AcNo = 1234) as calcdate
)
where calcdate between startDate and endDate;

edited Mar 11 '10 at 02:37

Jeffrey Kemp

59,135
14
106
158

answered Mar 10 '10 at 11:42

Tony Andrews

129,880
21
220
259

This will give ORA-00904: "caldate": invalid identifier – Sachin Mar 10 '10 at 11:45
2

@SachinChorasiya - If you had *looked* at Tony's sample you would have noticed that it contained a typo (which I have now corrected). It is a seriously bad idea to run code off the internet without properly reviewing first. – APC Mar 10 '10 at 12:08
Thanks for fixing that, Andrew. – Tony Andrews Mar 10 '10 at 13:07

score 3 · Answer 2 · answered Mar 10 '10 at 12:45

You could select your date from dual and join the results:

select * 
from   <<your table with startDate and endDate columns>> -- Since you ommited the main "from" clause from your statement
,      (
         select decode( :pValue
                      , 1, sysdate
                      , ( select activation_date from account where AcNo = 1234 )
                      ) as calDate
         from   dual
       ) c
where  c.calDate between startDate and endDate
... -- any other conditions you may need

How to use a calculated column in where condition?

2 Answers2