LenTompkins
asked on
Recursive Query
I need to write a query to update a table Billed with all activity for that record and any records previous to that date.
BillNO date Allow Payrec ADjAmt tot Type
1 1/1/2012 45.94 0 0 45 INV
1 1/10/2012 0 27.46 0 18.48 Chk
1 1/10/2012 0 18.48 0 0 Chk
2 1/2/2012 60.00 0 0 60.00 INV
2 1/5/2012 0 0 15.00 0 45.00 ADJ
2 1/6/2012 15.00 0 0 60.00 ADJ
2 2/7/2012 0 40.00 0 20.00 Chk
I need to take any entries in Allow and subtract any amounts in Payrec and AdjAmt and insert the total into tot. Then on the subsequent records take the Tot from the previous record and add or subtract the Allow, Payrec and AdjAmt to obtain a new total using Date to sort by.
How do I go about doing this? I believe I need a recursive query, but I don’t know how to go about writing this?
As a first attempt I wanted to just print out the records before I tried to accumulate totals:
This is what I wrote:
WITH BaseBillNO (BillNO_NoDash, Allowed, Payrec, AdjustAmt,DateBilled, HierarchyLevel) AS
(
-- Base case
SELECT
BillNO_NoDash,
Allowed,
Payrec,
AdjustAmt,
DateBilled,
1 as HierarchyLevel
FROM TBilled
WHERE Descript = 'INV'
and BillNO_NoDash = 56793 and DELFLAG = 0
UNION ALL
-- Recursive step
SELECT
B.BillNO_NoDash,
B.Allowed,
B.Payrec,
B.AdjustAmt,
B.DateBilled,
pr.HierarchyLevel + 1 AS HierarchyLevel
FROM (Select *
from TBILLED
where Descript <> 'INV' and BillNO_NoDash = 56793
)B
INNER JOIN BaseBillNO pr ON
B.BillNO_NoDash = pr.BillNO_NODash
)
SELECT *
FROM BaseBillNO
where BillNO_NoDash = 56793 and HierarchyLevel < 10
ORDER BY BillNO_NoDash, HierarchyLevel, Allowed, Payrec, AdjustAmt,DateBilled
I am getting an error: The maximum recursion 100 has been exhausted before statement completion.
Any thoughts would be greatly appreciated.
BillNO date Allow Payrec ADjAmt tot Type
1 1/1/2012 45.94 0 0 45 INV
1 1/10/2012 0 27.46 0 18.48 Chk
1 1/10/2012 0 18.48 0 0 Chk
2 1/2/2012 60.00 0 0 60.00 INV
2 1/5/2012 0 0 15.00 0 45.00 ADJ
2 1/6/2012 15.00 0 0 60.00 ADJ
2 2/7/2012 0 40.00 0 20.00 Chk
I need to take any entries in Allow and subtract any amounts in Payrec and AdjAmt and insert the total into tot. Then on the subsequent records take the Tot from the previous record and add or subtract the Allow, Payrec and AdjAmt to obtain a new total using Date to sort by.
How do I go about doing this? I believe I need a recursive query, but I don’t know how to go about writing this?
As a first attempt I wanted to just print out the records before I tried to accumulate totals:
This is what I wrote:
WITH BaseBillNO (BillNO_NoDash, Allowed, Payrec, AdjustAmt,DateBilled, HierarchyLevel) AS
(
-- Base case
SELECT
BillNO_NoDash,
Allowed,
Payrec,
AdjustAmt,
DateBilled,
1 as HierarchyLevel
FROM TBilled
WHERE Descript = 'INV'
and BillNO_NoDash = 56793 and DELFLAG = 0
UNION ALL
-- Recursive step
SELECT
B.BillNO_NoDash,
B.Allowed,
B.Payrec,
B.AdjustAmt,
B.DateBilled,
pr.HierarchyLevel + 1 AS HierarchyLevel
FROM (Select *
from TBILLED
where Descript <> 'INV' and BillNO_NoDash = 56793
)B
INNER JOIN BaseBillNO pr ON
B.BillNO_NoDash = pr.BillNO_NODash
)
SELECT *
FROM BaseBillNO
where BillNO_NoDash = 56793 and HierarchyLevel < 10
ORDER BY BillNO_NoDash, HierarchyLevel, Allowed, Payrec, AdjustAmt,DateBilled
I am getting an error: The maximum recursion 100 has been exhausted before statement completion.
Any thoughts would be greatly appreciated.
Hi Len,
Several things seem to be at work here.
1. Are you familiar with the concepts of combinations, permutations, and cartesian products? In your recursive query, the second subquery (the lower half) joins on the column *BillNO_NODash*. That causes a cartesian product when every row that matches on *BillNO_NODash* is joined to the results of the base query. When the recursive step occurs, every row that matches on *BillNO_NODash* is joined with the result of the first join. If there are 10 rows with the same value for *BillNO_NODash*, the base query generates 1 row, the lower half of the query generates 10 (for that value of *BillNO_NODash*) the first recursive pass generates 100, the next 1000, etc.
The lower sub-query needs to join ONE row from the table each pass. (Anything else is extremely complicated AND advanced.)
There are a couple of articles in the DB2 forum that describe this. The Common Table Expression (CTE) syntax is nearly identical between DB2 and SQL Server. The only real difference of note is that the concatenation operator in DB2 is double-pipe (||) and in SQL Server it's plus (+).
https://www.experts-exchange.com/Database/DB2/A_3629-Recursive-SQL-in-DB2-Converting-Columns-to-Rows.html
https://www.experts-exchange.com/Database/DB2/A_3618-Recursive-SQL-in-DB2-Converting-rows-to-columns.html
2. But you don't really need recursive SQL here. Recursive SQL puts multiple lines on a single row, or splits a single row into multiple lines. You seem to need aggregation here (SUM, COUNT, AVG, etc.) If you want to study recursive techniques, those articles will help. But I don't think that they're needed for this solution.
Good Luck,
Kent
Several things seem to be at work here.
1. Are you familiar with the concepts of combinations, permutations, and cartesian products? In your recursive query, the second subquery (the lower half) joins on the column *BillNO_NODash*. That causes a cartesian product when every row that matches on *BillNO_NODash* is joined to the results of the base query. When the recursive step occurs, every row that matches on *BillNO_NODash* is joined with the result of the first join. If there are 10 rows with the same value for *BillNO_NODash*, the base query generates 1 row, the lower half of the query generates 10 (for that value of *BillNO_NODash*) the first recursive pass generates 100, the next 1000, etc.
The lower sub-query needs to join ONE row from the table each pass. (Anything else is extremely complicated AND advanced.)
There are a couple of articles in the DB2 forum that describe this. The Common Table Expression (CTE) syntax is nearly identical between DB2 and SQL Server. The only real difference of note is that the concatenation operator in DB2 is double-pipe (||) and in SQL Server it's plus (+).
https://www.experts-exchange.com/Database/DB2/A_3629-Recursive-SQL-in-DB2-Converting-Columns-to-Rows.html
https://www.experts-exchange.com/Database/DB2/A_3618-Recursive-SQL-in-DB2-Converting-rows-to-columns.html
2. But you don't really need recursive SQL here. Recursive SQL puts multiple lines on a single row, or splits a single row into multiple lines. You seem to need aggregation here (SUM, COUNT, AVG, etc.) If you want to study recursive techniques, those articles will help. But I don't think that they're needed for this solution.
Good Luck,
Kent
ASKER
Thank you both for this information. I read the article on converting rows to columns and I had a problem.
1. I created the table and the query as follows and I am still getting an error:
CREATE TABLE rec
(
snum INTEGER, -- sentence number
wordnum INTEGER, -- word number in the sentence
word VARCHAR(100) -- word being saved
);
INSERT INTO rec
VALUES (1, 1, 'This'), (1, 2, 'is'), (1, 3, 'a'), (1, 4, 'fine'), (1, 5, 'example');
Select * from Rec
WITH rquery (snum, wordnum, sentence)
AS
(
SELECT base.snum, base.wordnum, base.word
FROM rec base
WHERE wordnum = 1
UNION ALL
SELECT t1.snum, t1.wordnum, t0.sentence + ' ' + t1.word
FROM rquery t0, rec t1
WHERE t0. snum = t1. snum
AND t0.wordnum + 1 = t1.wordnum
)
SELECT *
FROM rquery;
Types don't match between the anchor and the recursive part in column "sentence" of recursive query "rquery"
Can you tell me what is wrong?
2. I tried the query that dqmq posted and that returns one record. I simplied the test, by creating a table just like what was stated above:
USE [SleepMgt]
GO
CREATE TABLE [dbo].[ttBilled](
[BillNO] [int] NULL,
[Allowed] [numeric](19, 2) NULL,
[Payrec] [numeric](19, 2) NULL,
[AdjustAmt] [numeric](19, 2) NULL,
[DateBilled] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[ttBilled] ([BillNO], [Allowed], [Payrec], [AdjustAmt], [DateBilled]) VALUES (1, CAST(45.00 AS Numeric(19, 2)), NULL, NULL, CAST(0x00009FCB00000000 AS DateTime))
INSERT [dbo].[ttBilled] ([BillNO], [Allowed], [Payrec], [AdjustAmt], [DateBilled]) VALUES (1, NULL, NULL, CAST(25.00 AS Numeric(19, 2)), CAST(0x00009FEA00000000 AS DateTime))
INSERT [dbo].[ttBilled] ([BillNO], [Allowed], [Payrec], [AdjustAmt], [DateBilled]) VALUES (1, NULL, NULL, CAST(20.00 AS Numeric(19, 2)), CAST(0x00009FF800000000 AS DateTime))
WITH BaseBillNO (BillNO, Allowed, Payrec, AdjustAmt,DateBilled, Tot) AS
(
-- Filtered case
SELECT
T.BillNO,
Allowed,
Payrec,
AdjustAmt,
DateBilled,
isNull(ALLOWED,0) - isNull(PAYREC,0) - isNull(ADJUSTAMT,0) as tot
FROM (Select Min(DateBilled) as minDateBilled, BillNO
from TTBilled
Group by BillNO
) A
inner join tTBILLED T
on A.BillNO = T.BillNO and A.minDateBilled = T.DATEBILLED
)
SELECT *,
(Select sum(isNull(f.allowed,0) - IsNull(f.payrec,0) - isNull(f.AdjustAmt,0)) from BaseBillNO f
where f.billNO = b.BillNO
and b.DateBilled <= f.dateBilled
group by f.billNO
) as Tot
FROM BaseBillNO B
order by b.DateBilled
This doesn't abend, but it is not giving me a proper answer.
BillNO Allowed Payrec AdjustAmt DateBilled Tot Tot
1 45.00 NULL NULL 2012-01-01 00:00:00.000 45.00 45.00
How do I debug these queries? I suspect I have something wrong with the lower select statement.
Thanks for giving me some assistance.
1. I created the table and the query as follows and I am still getting an error:
CREATE TABLE rec
(
snum INTEGER, -- sentence number
wordnum INTEGER, -- word number in the sentence
word VARCHAR(100) -- word being saved
);
INSERT INTO rec
VALUES (1, 1, 'This'), (1, 2, 'is'), (1, 3, 'a'), (1, 4, 'fine'), (1, 5, 'example');
Select * from Rec
WITH rquery (snum, wordnum, sentence)
AS
(
SELECT base.snum, base.wordnum, base.word
FROM rec base
WHERE wordnum = 1
UNION ALL
SELECT t1.snum, t1.wordnum, t0.sentence + ' ' + t1.word
FROM rquery t0, rec t1
WHERE t0. snum = t1. snum
AND t0.wordnum + 1 = t1.wordnum
)
SELECT *
FROM rquery;
Types don't match between the anchor and the recursive part in column "sentence" of recursive query "rquery"
Can you tell me what is wrong?
2. I tried the query that dqmq posted and that returns one record. I simplied the test, by creating a table just like what was stated above:
USE [SleepMgt]
GO
CREATE TABLE [dbo].[ttBilled](
[BillNO] [int] NULL,
[Allowed] [numeric](19, 2) NULL,
[Payrec] [numeric](19, 2) NULL,
[AdjustAmt] [numeric](19, 2) NULL,
[DateBilled] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[ttBilled] ([BillNO], [Allowed], [Payrec], [AdjustAmt], [DateBilled]) VALUES (1, CAST(45.00 AS Numeric(19, 2)), NULL, NULL, CAST(0x00009FCB00000000 AS DateTime))
INSERT [dbo].[ttBilled] ([BillNO], [Allowed], [Payrec], [AdjustAmt], [DateBilled]) VALUES (1, NULL, NULL, CAST(25.00 AS Numeric(19, 2)), CAST(0x00009FEA00000000 AS DateTime))
INSERT [dbo].[ttBilled] ([BillNO], [Allowed], [Payrec], [AdjustAmt], [DateBilled]) VALUES (1, NULL, NULL, CAST(20.00 AS Numeric(19, 2)), CAST(0x00009FF800000000 AS DateTime))
WITH BaseBillNO (BillNO, Allowed, Payrec, AdjustAmt,DateBilled, Tot) AS
(
-- Filtered case
SELECT
T.BillNO,
Allowed,
Payrec,
AdjustAmt,
DateBilled,
isNull(ALLOWED,0) - isNull(PAYREC,0) - isNull(ADJUSTAMT,0) as tot
FROM (Select Min(DateBilled) as minDateBilled, BillNO
from TTBilled
Group by BillNO
) A
inner join tTBILLED T
on A.BillNO = T.BillNO and A.minDateBilled = T.DATEBILLED
)
SELECT *,
(Select sum(isNull(f.allowed,0) - IsNull(f.payrec,0) - isNull(f.AdjustAmt,0)) from BaseBillNO f
where f.billNO = b.BillNO
and b.DateBilled <= f.dateBilled
group by f.billNO
) as Tot
FROM BaseBillNO B
order by b.DateBilled
This doesn't abend, but it is not giving me a proper answer.
BillNO Allowed Payrec AdjustAmt DateBilled Tot Tot
1 45.00 NULL NULL 2012-01-01 00:00:00.000 45.00 45.00
How do I debug these queries? I suspect I have something wrong with the lower select statement.
Thanks for giving me some assistance.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Kent Thanks that solved that problem. How do I debug the query in question 2? I tried using the same idea for this problem and I'm getting this error:
WITH BaseBillNO (BillNO, Allowed, Payrec, AdjustAmt,DateBilled, Tot) AS
(
-- Filtered case
SELECT
T.BillNO,
Allowed,
Payrec,
AdjustAmt,
DateBilled,
isNull(ALLOWED,0) - isNull(PAYREC,0) - isNull(ADJUSTAMT,0) as tot
FROM (Select Min(DateBilled) as minDateBilled, BillNO
from TTBilled
Group by BillNO
) A
inner join tTBILLED T
on A.BillNO = T.BillNO and A.minDateBilled = T.DATEBILLED
Union all
(Select F.BillNO,F.Allowed, F.Payrec, F.AdjustAmt, F.DateBilled, B.tot
from ( Select BILLNO, Allowed, Payrec, AdjustAmt, DateBilled
from ttBilled
) f, BaseBillNO B
where f.billNO = b.BillNO
and b.DateBilled <= f.dateBilled
)
)
Select * from BaseBillNO
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Can I put a print statement in this somewhere? If so where?
WITH BaseBillNO (BillNO, Allowed, Payrec, AdjustAmt,DateBilled, Tot) AS
(
-- Filtered case
SELECT
T.BillNO,
Allowed,
Payrec,
AdjustAmt,
DateBilled,
isNull(ALLOWED,0) - isNull(PAYREC,0) - isNull(ADJUSTAMT,0) as tot
FROM (Select Min(DateBilled) as minDateBilled, BillNO
from TTBilled
Group by BillNO
) A
inner join tTBILLED T
on A.BillNO = T.BillNO and A.minDateBilled = T.DATEBILLED
Union all
(Select F.BillNO,F.Allowed, F.Payrec, F.AdjustAmt, F.DateBilled, B.tot
from ( Select BILLNO, Allowed, Payrec, AdjustAmt, DateBilled
from ttBilled
) f, BaseBillNO B
where f.billNO = b.BillNO
and b.DateBilled <= f.dateBilled
)
)
Select * from BaseBillNO
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Can I put a print statement in this somewhere? If so where?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all of the information. I got the results I needed for my specific post from the last post and I appreciated getting the information about recursive queries.
WITH BaseBillNO (BillNO_NoDash, Allowed, Payrec, AdjustAmt,DateBilled) AS
(
-- Filtered case
SELECT
BillNO_NoDash,
Allowed,
Payrec,
AdjustAmt,
DateBilled
FROM TBilled
WHERE Descript = 'INV'
and BillNO_NoDash = 56793 and DELFLAG = 0
)
SELECT *,
(Select sum(f.allowed - f.payrec - f.AdjustAmount) from BaseBillNO f
where f.billNO_NoDash = b.BillNO_NoDash
and f.DateBilled <= b.dateBilled
group by f.billNO_NoDash
) as Tot
FROM BaseBillNO B
order by b.DateBilled