Recursive CTE - Batches

  • koti.raavi

    SSCrazy

    Points: 2688

    Hi Folks

    I'm trying to group rows into batch and adding batch to query to fetch data from system. I know there is lot of questions why it is required. I'm trying fetch data from Oracle SQL developer.  The issue is last row of batch is repeating in next batch. I added query for better understanding.  Any help much appreciated .

     

    Create Table #TEMP
    (
    ProductID INT
    )

    INSERT INTO #TEMP
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7

    Declare @Count1 int = 0
    Declare @a int = 1
    select @Count1 = COUNT(*) FROM #TEMP

    ;WITH CTE AS
    (

    SELECT ProductID,
    ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) AS ROWNUM
    FROM #TEMP
    ),
    CTE2 as
    (

    select @a as B,
    Convert(varchar(max),'') as Product_Query

    union all

    SELECT B+3,

    CONVERT(VARCHAR(MAX),'WITH CTE AS ('+REPLACE((SELECT 'SELECT ' +CONVERT(VARCHAR,ProductID) +' AS ProductID FROM #TEMP '+' UNION ALL '
    from CTE where RowNum between B and B+3
    for xml path(''))+';;;',' UNION ALL '+';;;','')+')'
    +'SELECT
    CTE.ProductID
    ,PS.Product_Name
    FROM CTE CTE
    INNER JOIN ProductSales PS ON CTE.ProductID=PS.ProductID
    ') AS C
    from Cte2 where B <= @Count1
    )
    select Product_Query from Cte2
    where len(Product_Query)>0
    OPTION (MAXRECURSION 0)
  • John Mitchell-245523

    SSC Guru

    Points: 148652

    If you change @a to @a-1 and B to B+1 as in this snippet, you don't get the overlaps.

    ...
    CTE2 as
    (

    select @a - 1 as B,
    Convert(varchar(max),'') as Product_Query

    union all

    SELECT B+3,

    CONVERT(VARCHAR(MAX),'WITH CTE AS ('+REPLACE((SELECT 'SELECT ' +CONVERT(VARCHAR,ProductID) +' AS ProductID FROM #TEMP '+' UNION ALL '
    from CTE where RowNum between B+1 and B+3
    for xml path(''))+';;;',' UNION ALL '+';;;','')+')'
    +'SELECT
    CTE.ProductID
    ,PS.Product_Name
    FROM CTE CTE
    INNER JOIN ProductSales PS ON CTE.ProductID=PS.ProductID
    ') AS C
    from Cte2 where B <= @Count1
    )
    ...

    John

  • koti.raavi

    SSCrazy

    Points: 2688

    Awesome! Thank you John! Much Appreciated for you're help.

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

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