Oracle sql WITH clause and EXTRACT function -

i trying understand difference between 2 queries below. second 1 throws syntax error. can please?

1.

select    extract(year (sysdate - to_date('2002-12-25')) year month) y  dual; 

2.

with age(d) (   select (sysdate - to_date('2002-12-25')) dual ) select    extract(year (age.d) year month) y  age; 

error: ora-30083: syntax error found in interval value expression 30083. 00000 - "syntax error found in interval value expression" *cause: syntax error found during parsing interval value

your question simplified bit to, why work:

select (sysdate - date '2002-12-25') year month dual;  (sysda ------ +14-03 

but plugging same number produced sysdate - date '2002-12-25' same call not, or without parentheses in various combinations:

select (5217.4197) year month dual;  ora-30083: syntax error found in interval value expression 

the problem 2 numbers different internally. @hinoff suggested, can use dump() function examine them:

select dump(sysdate - date '2002-12-25') dual;  dump(sysdate-date'2002-12-25') ----------------------------------- typ=14 len=8: 97,20,0,0,195,143,0,0  select dump(5217.4197) dual;  dump(5217.4197)              ---------------------------- typ=2 len=5: 194,53,18,42,98 

type 2 documented number. , further explain in mos document id 1031902.6.

you same error if explicitly converted calculation type-2 number:

select (cast(sysdate - date '2002-12-25' number)) year month dual;  ora-30083: syntax error found in interval value expression 

type 14 seems undocumented internal representation, , oracle able - internally - convert , interval in way can't normal type-2 number.

you can convert number interval numtodsinterval(5217.4197, 'day'), because day-to-second , year-to-month intervals aren't compatible can't extract number of years (which makes sense, number of days in year varies).

you'd see similar issues if tried avoid timestamp instead of dates:

select (systimestamp - timestamp '2002-12-25 00:00:00') year month dual;  (systi ------ +14-03  select dump(systimestamp - timestamp '2002-12-25 00:00:00') dual;  dump(systimestamp-timestamp'2002-12-2500:00:00') ------------------------------------------------------------------------ typ=190 len=24: 97,20,0,0,9,0,0,0,38,0,0,0,44,0,0,0,24,177,20,9,10,0,0,0 

which isn't 1 of documented interval types, can internally converted either year-to-month or day-to-second. trying use in cte fails; using extract(year age.d) gets "ora-30076: invalid extract field extract source" because it's seen ds interval @ point, , extract(year (age.d) year month) ora-30083.

you might better off switching intervals other units, such months:

with age(m) (   select months_between(sysdate, date '2002-12-25') dual ) select    trunc(age.m / 12) y age;           y ----------         14 

if want number of months use trunc(remainder(age.m, 12)).

or if want interval, convert in cte:

with age(ym) (   select numtoyminterval(     months_between(sysdate, date '2002-12-25'), 'month') dual ) select    extract(year age.ym) y  age; 

or closer original:

with age(ym) (   select (sysdate - date '2002-12-25') year month dual ) select    extract(year age.ym) y  age; 

interestingly, the documentation suggests isn't allowed - "the 6 combinations yield interval values valid in interval expression", , date-date isn't one of six. might suggest type 14 closer interval number type.