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)
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
January 29, 2020 at 12:25 pm
Awesome! Thank you John! Much Appreciated for you're help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply