N1QL recursive query not work as needed

SQL++
1

I want to prepare a pre-recursive query like I wrote in sql server, but I can’t get a response like in sql server. Anyone who knows about this, please help me.

SQL QUERY :

;WITH Dates
AS (SELECT Cast(dateadd(day,-7,Getdate()) AS DATE) AS Tarih
UNION ALL
SELECT Dateadd(day, 1, Tarih)
FROM Dates
WHERE Tarih < cast(Getdate() as date)
)

SELECT *
FROM Dates a

RESULT :

Tarih
2022-10-05
2022-10-06
2022-10-07
2022-10-08
2022-10-09
2022-10-10
2022-10-11
2022-10-12

N1QL QUERY :

WITH Dates As (

        SELECT DATE_ADD_STR(CLOCK_LOCAL("1111-11-11"), -7, 'day') As Tarih 
        UNION
        SELECT DATE_ADD_STR(B.Tarih, 1, 'day')
        FROM   Dates AS B
        WHERE  B.Tarih < CLOCK_LOCAL("1111-11-11")
       )

Select * From Dates

RESULT :

[
{
“Dates”: {
“Tarih”: “2022-10-05”
}
}
]

2

Probably not the best way, but a way, for the specific example:

WITH dates AS (SELECT * FROM date_range_str(date_add_str(clock_local("1111-11-11"),-7,'day'), date_add_str(clock_local("1111-11-11"),1,'day'), 'day') AS Tarih)
SELECT dates.* FROM dates;

Not a recursive CTE as you desire though.

1 Like
3

Couchbase doesn’t support recursive CTEs. You can solve individual cases with different approaches (like the above) or emulate it with UDFs etc.

HTH.

1 Like
4

Track via MB-11512

5

Thank you both very much for giving useful and useful information to me.
Kind regards,