Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
WITH Dates  AS
(
 SELECT VtoDate AS BusinessDay, 0 AS DaysCount from dual
 UNION ALL
 SELECT dateadd(dd,-1,BusinessDay) AS BusinessDay ,
 DaysCount + (CASE WHEN datepart(weekday, dateadd(dd,-1,BusinessDay)) NOT IN (7,1) THEN 1 ELSE 0 END) DaysCount
 FROM Dates 
 WHERE DaysCount < VnumberOfDays
)

SELECT  MIN(BusinessDay)into VfromDate FROM Dates;


What I have tried:

I am having this error
ORA-32039: recursive WITH clause must have column alias list
Posted
Updated 27-Jan-22 2:25am
v2
Comments
Bryian Tan 24-Dec-18 10:41am    
Have you try something like
WITH Dates (BusinessDay, DaysCount)  AS ...
ZurdoDev 27-Dec-18 21:04pm    
Just google the error. There's tons of answers online. You could have found the answer faster than the time it took you to post this.

This error message is quite good explained here: What does this mean?  ORA-32039: recursive WITH... | Oracle Community[^]
 
Share this answer
 
Comments
Wendelius 28-Dec-18 16:06pm    
Yup, and the error message it's quite self explanatory...
Maciej Los 29-Dec-18 15:52pm    
Thank you, Mika.
The reason Oracle is reading the query as recursive is because the Temp table name is Dates and the table name in the second select is also named Dates. Just rename the temp table something different (i.e. Dates1, etc.,). ;-)

YOUR CODE
Line 1: WITH Dates
...
Line 8: FROM Dates
....

Line 12: ... FROM Dates;

WORKING EXAMPLE
Line 1: WITH Dates1
...
Line 8: FROM Dates
....

Line 12: ... FROM Dates1;


I hope this was helpful!
 
Share this answer
 
Comments
Richard Deeming 27-Jan-22 8:35am    
Absolute nonsense. The code in the already solved question is using a recursive CTE, not a temp table.

If you're going to resurrect an old solved question by posting a new solution, make sure you have read and understood the question and the accepted solution, and that you are adding something new to the discussion.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900