I am trying to get Day of week and Date recursively using with clause.
I am able to get the desired output using below query, but when I put this query inside PL/SQL procedure It is giving compile time error "PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got –" in Oracle 11.2.0.3.0 and getting compiled successfully in 11.2.0.2.0.
WITH
GetDatesCTE ( N, TestWeek, TestDate, TestDay ) AS
(
SELECT
1 N ,
1 TestWeek ,
cast(sysdate as date) TestDate ,
1 TestDay
FROM
DUAL
UNION ALL
SELECT
N + 1 , -- Error : PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got while compiling in 11.2.0.3.0
CASE
WHEN to_char(dateadd('DD', 1, TESTDATE),'D') = 2
THEN TestWeek + 1
ELSE TestWeek
END col ,
dateadd('DD', 1, TESTDATE) ,
CASE
WHEN to_char(dateadd('DD', 1,TESTDATE),'D') = 2
THEN 1
ELSE TestDay + 1
END col
FROM
GetDatesCTE
WHERE
TestDate < sysdate + 10
)
Select * from GetDatesCTE;
Please help here and let me know why compiler is behaving differently in different oracle installations.
Thanks in advance.