我有一个查询,该查询旨在显示表A中最近没有足够更新的行。(每行应在“month_no”之后的2个月内更新。):
SELECT A.identifier
, A.name
, TO_NUMBER(DECODE( A.month_no
, 1, 200803
, 2, 200804
, 3, 200805
, 4, 200806
, 5, 200807
, 6, 200808
, 7, 200809
, 8, 200810
, 9, 200811
, 10, 200812
, 11, 200701
, 12, 200702
, NULL)) as MONTH_NO
, TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
FROM table_a A
, table_b B
WHERE A.identifier = B.identifier
AND MONTH_NO > UPD_DATE
WHERE子句中的最后一行导致"ORA-00904无效标识符“错误。不用说,我不想在WHERE子句中重复整个解码函数。有什么想法吗?(修复和变通方法都接受...)
发布于 2012-01-06 14:48:21
SELECT A.identifier
, A.name
, TO_NUMBER(DECODE( A.month_no
, 1, 200803
, 2, 200804
, 3, 200805
, 4, 200806
, 5, 200807
, 6, 200808
, 7, 200809
, 8, 200810
, 9, 200811
, 10, 200812
, 11, 200701
, 12, 200702
, NULL)) as MONTH_NO
, TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
FROM table_a A, table_b B
WHERE .identifier = B.identifier
HAVING MONTH_NO > UPD_DATE
发布于 2009-10-15 18:57:39
或者,您可以在HAVING
子句中使用别名
发布于 2017-02-14 13:49:23
就像你可以做的另一种方法一样:
WITH inner_table AS
(SELECT A.identifier
, A.name
, TO_NUMBER(DECODE( A.month_no
, 1, 200803
, 2, 200804
, 3, 200805
, 4, 200806
, 5, 200807
, 6, 200808
, 7, 200809
, 8, 200810
, 9, 200811
, 10, 200812
, 11, 200701
, 12, 200702
, NULL)) as MONTH_NO
, TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
FROM table_a A
, table_b B
WHERE A.identifier = B.identifier)
SELECT * FROM inner_table
WHERE MONTH_NO > UPD_DATE
您还可以为您的队列创建一个永久视图并从视图中进行选择。
CREATE OR REPLACE VIEW_1 AS (SELECT ...);
SELECT * FROM VIEW_1;
https://stackoverflow.com/questions/356675
复制相似问题