WITH RECURSIVE gives error

Question

Post reply

WITH RECURSIVE gives error

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
July 7, 2023 at 6:20 pm

#4229479

WITH RECURSIVE factorial(F,n) AS (
SELECT 1 F, 3 n
UNION ALL
SELECT F*n F, n-1 n from factorial where n>1
)
SELECT F from factorial where n=1

Error
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near 'factorial'.
Completion time: 2023-07-07T14:16:59.5379702-04:00
If i remove RECURSIVE option it works fine.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16215 More actions · Answer 1

with recursive is not a MS SQL option. It is available on other DBMS like Postgres.

ratbak Default port Points: 1407 More actions · Answer 2

Did you mean to use OPTION (MAXRECURSION)?

i.e., something like

WITH factorial(F,n) AS (
SELECT 1 F, 3 n
UNION ALL
SELECT F*n F, n-1 n from factorial where n>1
)
SELECT F from factorial where n=1
OPTION (MAXRECURSION n) -- where n is a value between 0 and 32,767

In this case, there's no reason for n to be larger than 2.

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 3

I think you might want a recursive common table expression.

Something like this:

DECLARE @n int = 3;
WITH Factorial(F,n) AS 
(
    SELECT 1      AS F, 
           @n     AS n
     UNION ALL
    SELECT F*n    AS F, 
           n - 1  AS n 
      FROM Factorial 
     WHERE n > 1
)
SELECT F
  FROM Factorial 
 WHERE n = 1
;

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 4

Jonathan AC Roberts wrote:

I think you might want a recursive common table expression.

Something like this:

DECLARE @n int = 3;
WITH Factorial(F,n) AS 
(
    SELECT 1      AS F, 
           @n     AS n
     UNION ALL
    SELECT F*n    AS F, 
           n - 1  AS n 
      FROM Factorial 
     WHERE n > 1
)
SELECT F
  FROM Factorial 
 WHERE n = 1
;

I could be wrong but it looks like they were already using a Recursive CTE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 5

Jeff Moden wrote:

Jonathan AC Roberts wrote:

I think you might want a recursive common table expression.

Something like this:

DECLARE @n int = 3;
WITH Factorial(F,n) AS 
(
    SELECT 1      AS F, 
           @n     AS n
     UNION ALL
    SELECT F*n    AS F, 
           n - 1  AS n 
      FROM Factorial 
     WHERE n > 1
)
SELECT F
  FROM Factorial 
 WHERE n = 1
;

I could be wrong but it looks like they were already using a Recursive CTE.

Yes, but it wouldn't work with the word "RECURSIVE" in it.

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 6

Ah... I see my confusion here. The op posted a recursive CTE where he said it wouldn't work with the word RECURSIVE and Frederico said that RECURSIVE is not an SQL option. My brain short-cut that to meaning that's the way SQL Server does things and the word RECURSIVE isn't necessary so do it that way.

Then, Ratbak posted a recursive CTE and I took that as the right way to do it but, up to your post, no one told the OP was it was called in SQL Server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 7

Jeff Moden wrote:

Ah... I see my confusion here. The op posted a recursive CTE where he said it wouldn't work with the word RECURSIVE and Frederico said that RECURSIVE is not an SQL option. My brain short-cut that to meaning that's the way SQL Server does things and the word RECURSIVE isn't necessary so do it that way.
Then, Ratbak posted a recursive CTE and I took that as the right way to do it but, up to your post, no one told the OP was it was called in SQL Server.

Yes, I don't think I looked at ratbak's answer. Too often I just type an answer without reading the previous responses.

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 8

Jonathan AC Roberts wrote:

Yes, I don't think I looked at ratbak's answer. Too often I just type an answer without reading the previous responses.

Got it. I've had the same problem in the past especially if the discussion is across several pages.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WITH RECURSIVE gives error

Cookies on SQLServerCentral