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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy