如何在where条件中使用计算列？

Question

如何在Oracle 9i中在where条件中使用计算列?

我想要像下面这样使用：

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

- Sachin Chourasiya

Answer 1

您可以使用内联视图：

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;

- Tony Andrews

---

这将返回ORA-00904: "caldate"：无效的标识符。 - Sachin Chourasiya

@SachinChorasiya - 如果您查看了Tony的样例，您会注意到其中有一个打字错误（我已经纠正了）。在未经适当审查之前从互联网运行代码是非常糟糕的想法。 - APC

感谢您修复了这个问题，安德鲁。 - Tony Andrews

Answer 2

您可以从 dual 中选择日期并将结果连接起来：

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

- R. Genaro