调用自定义dow()函数处理first_day/last_day时提示无效标识符求助

你遇到的「无效标识符」错误，是因为SQL的执行顺序限制：SELECT子句里定义的列别名（比如first_day、last_day），不能在同一级的SELECT中直接引用——数据库在处理函数调用时，还没完成这些别名的解析，所以会把它们当成未定义的标识符。

解决方法一：直接传入计算表达式 #

不用依赖别名，把生成日期的表达式直接作为dow()函数的参数：

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

CREATE OR REPLACE FUNCTION dow(P_DAY DATE) RETURN VARCHAR2
IS
BEGIN
    RETURN(TO_CHAR(P_DAY,'DAY'));
END;
/

with starting_date (datum) as
     (select add_months(trunc(sysdate, 'yyyy'), 12) from dual)
select add_months(datum, level - 1)  first_day,
       dow(add_months(datum, level - 1)),
       last_day(add_months(datum, level - 1)) as last_day,
       dow(last_day(add_months(datum, level - 1)))
from starting_date
connect by level <= 12;

解决方法二：用子查询提前生成日期列 #

先在子查询里算出first_day和last_day，外层再调用dow()函数，这样就能正常引用别名：

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

CREATE OR REPLACE FUNCTION dow(P_DAY DATE) RETURN VARCHAR2
IS
BEGIN
    RETURN(TO_CHAR(P_DAY,'DAY'));
END;
/

with starting_date (datum) as
     (select add_months(trunc(sysdate, 'yyyy'), 12) from dual),
     month_dates as (
         select add_months(datum, level - 1)  first_day,
                last_day(add_months(datum, level - 1)) as last_day
         from starting_date
         connect by level <= 12
     )
select first_day,
       dow(first_day),
       last_day,
       dow(last_day)
from month_dates;

内容的提问来源于stack exchange，提问作者Pugzly