I'm getting invalid identifier when trying to display the dow() for first_day and last_day.

Can someone please help me out.

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(first_day),
          last_day(add_months(datum, level - 1)) as last_day,
            dow(last_day)
    from starting_date
    connect by level <= 12;

CodePudding user response：

You can't use a column alias in the same level of query it is defined (except in an order-by clause). You would need to either use another subquery to get the first_day and last_day values and then call dow() for those in an outer query, or just repeat the calculations:

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

fiddle

It's also not generally a good idea to use function names or other keywords as object names, including aliases; it's not complaining here, but I'd still consider calling the column something other than last_day.

You should also be aware the converting a date to a day name (among other formats) is affected by the session NLS settings, so someone else running this might see the names in a different language. And by default the day names are padded with spaces, which you might not be expecting.