Stored Procedure com CTE Firebird 2.5

[Rodrigo Cardilo]

Bom dia,

Alguém sabe como uso o recurso abaixo numa Stored Procedure ou View?

WITH RECURSIVE
DATES (DT) AS (
SELECT CURRENT_DATE
FROM RDB$DATABASE
UNION ALL
SELECT DT + 1 FROM DATES
WHERE DT < CURRENT_DATE + 31
)
SELECT * FROM DATES

 

[BigWings]

Não sei se entendi bem a dúvida ou o propósito, mas testei dessa forma e rodou:

CREATE OR ALTER PROCEDURE SP_DATES 
returns (
    D date)
as
begin
FOR WITH RECURSIVE
DATES (DT) AS (
SELECT CURRENT_DATE
FROM RDB$DATABASE
UNION ALL
SELECT DT + 1 FROM DATES
WHERE DT < CURRENT_DATE + 31
)
SELECT DT FROM DATES INTO :D do
 suspend;
end

E:

CREATE VIEW VW_DATES(
    D)
AS
WITH RECURSIVE
DATES (DT) AS (
SELECT CURRENT_DATE
FROM RDB$DATABASE
UNION ALL
SELECT DT + 1 FROM DATES
WHERE DT < CURRENT_DATE + 31
)
SELECT DT FROM DATES
;

 

[Juliomar Marchetti]

Será preciso fazer a geração dos dias que não tem movimento nesse periodo. não lembro se o FB tem