We help IT Professionals succeed at work.

# SQl Recursive problem

on
This is my first attempt at a recursive query and it is not working
I am getting :  Conversion failed when converting the varchar
value '15286, ' to data type int.  Where is the , coming from?
Here is the code:
WITH CTE ( CorrBillNO, CopayList, BillNO, length )
AS ( SELECT CorrBillNO, CAST( '' AS VARCHAR(255) ), CAST( '' AS VARCHAR(255) ), 0
FROM TempTbl1

GROUP BY CorrBillNO
UNION ALL
SELECT p.CorrBillNO, CAST( CopayList +
CASE WHEN length = 0
THEN ''
ELSE ', '
END + p.BillNO AS VARCHAR(255) ),
CAST( p.BillNO AS VARCHAR(255)) , length + 1
FROM CTE c
INNER JOIN TempTbl1  p
ON c.CorrBillNO = p.CorrBillNO
WHERE p.BillNO > c.BillNO )

SELECT CorrBillNO, CopayList
FROM ( SELECT CorrBillNO, CopayList,
RANK() OVER ( PARTITION BY CorrBillNO ORDER BY length DESC )
FROM CTE ) D ( CorrBillNO, CopayList, rank )
WHERE rank = 1

Can you give me an idea of how to break this down to see where
the problem is occurring?
Thanks
Comment
Watch Question

## View Solution Only

Commented:
I was able to get past that problem with the following code but it seems to be in a loop, since
it ran over 5 minutes and it is looking at 12,000 records.  Also the majority of records will have only
one BillNO for each corrBillNO.  How do you debug this?

WITH CTE ( CorrBillNO, CopayList, BillNO, length )
AS ( SELECT CorrBillNO, CAST( '' AS VARCHAR(255) ), CAST( '' AS VARCHAR(255) ), 0
FROM TempTbl1

GROUP BY CorrBillNO
UNION ALL
SELECT p.CorrBillNO, CAST( CopayList +
CASE WHEN length = 0
THEN ''
ELSE ', '
END + p.BillNO AS VARCHAR(255) ),
CAST( p.BillNO AS VARCHAR(255)) , length + 1
FROM CTE c
INNER JOIN (Select  corrBillNO, Convert(varchar,BillNO) as BillNO
from TempTbl1
)        p
ON c.CorrBillNO = p.CorrBillNO
WHERE p.BillNO > c.BillNO )

SELECT CorrBillNO, CopayList
FROM ( SELECT CorrBillNO, CopayList,
RANK() OVER ( PARTITION BY CorrBillNO ORDER BY length DESC )
FROM CTE ) D ( CorrBillNO, CopayList, rank )
WHERE rank = 1
Sr. System Analyst
CERTIFIED EXPERT

Commented:
looks like it is coming from here

CAST( CopayList +
CASE WHEN length = 0
THEN ''
ELSE ', '
END + p.BillNO AS VARCHAR(255) ),
Sr. System Analyst
CERTIFIED EXPERT

Commented:
is p.BillNO varchar? if not cast that one to varchar too...
Sr. System Analyst
CERTIFIED EXPERT

Commented:
how to debug? oops, put some filters to your query so it runs very quick on a small set of data...

FROM TempTbl1
GROUP BY CorrBillNO

-->

FROM TempTbl1
WHERE some filters here
GROUP BY CorrBillNO

Commented:
That is what I thought, but I am doing a comparison against corrBillNO to CorrBillNO and
BillNO > BillNO.  As I stated in my second post, changing the inner join p to convert the
BillNO to varchar got rid of that problem, but it seems to be in a loop now.  How do I debug this?

Commented:
You need to run the Sub queries separately.
Using the With CTE part makes the whole query dependent on it. If that doesn't work then the whole query fails.

Try running the bolded and italicized query separately to see which on fails.

SELECT
CorrBillNO
, CAST( '' AS VARCHAR(255) ) AS "CopayList"
, CAST( '' AS VARCHAR(255) ) AS "BillNO"
, 0 AS "length"
FROM TempTbl1
GROUP BY CorrBillNO

UNION ALL

SELECT
p.CorrBillNO
, CAST( CopayList + CASE WHEN length = 0
THEN '' ELSE ', '
END + p.BillNO AS VARCHAR(255)
) AS "CopayList"
, CAST( p.BillNO AS VARCHAR(255)) AS "BillNO"
, length + 1
FROM CTE c

INNER JOIN TempTbl1  p  ON (c.CorrBillNO = p.CorrBillNO)
WHERE p.BillNO > c.BillNO
CERTIFIED EXPERT

Commented:
>>How do you debug this?

use OPTION MAXRECURSION to limit the number of iterations.

``````WITH CTE ( CorrBillNO, CopayList, BillNO, length )
AS ( SELECT CorrBillNO, CAST( '' AS VARCHAR(255) ), CAST( '' AS VARCHAR(255) ), 0
FROM TempTbl1

GROUP BY CorrBillNO
UNION ALL
SELECT p.CorrBillNO, CAST( CopayList +
CASE WHEN length = 0
THEN ''
ELSE ', '
END + p.BillNO AS VARCHAR(255) ),
CAST( p.BillNO AS VARCHAR(255)) , length + 1
FROM CTE c
INNER JOIN (Select  corrBillNO, Convert(varchar,BillNO) as BillNO
from TempTbl1
)        p
ON c.CorrBillNO = p.CorrBillNO
WHERE p.BillNO > c.BillNO )

SELECT CorrBillNO, CopayList
FROM ( SELECT CorrBillNO, CopayList,
RANK() OVER ( PARTITION BY CorrBillNO ORDER BY length DESC )
FROM CTE ) D ( CorrBillNO, CopayList, rank )
WHERE rank = 1
option (maxrecursion 10)
``````

Now I think you can do this without a CTE. try the below:

``````select CorrBillNo,
CopayList,
from (
select 	distinct
p.CorrBillNo,
stuff((select ', ' + BillNo from TempTbl1 where CorrBillNo = p.corrBillNo) as CopayList
from TempTbl1 p
)
where len(CopayList - replace(CopayList, ',', '')) = 1
``````
CERTIFIED EXPERT

Commented:
oops, second suggestion should be:
``````select CorrBillNo,
CopayList,
from (
select 	distinct
p.CorrBillNo,
stuff((select ', ' + BillNo from TempTbl1 where CorrBillNo = p.corrBillNo for xml path('')) as CopayList
from TempTbl1 p
) t1
where len(CopayList - replace(CopayList, ',', '')) = 1
``````
CERTIFIED EXPERT

Commented:
actually this should be better:

``````select CorrBillNo,
max(CopayList),
from (
select 	distinct
p.CorrBillNo,
stuff((select ', ' + BillNo from TempTbl1 where CorrBillNo = p.corrBillNo for xml path('')) as CopayList
from TempTbl1 p
) t1
group by CorrBillNo
``````

Commented:
The query works fine with one record and it is fast, when I ran it with all of the records, it ran for
over 10 minutes and still didn't finish.  I tried breaking down the two queries and the Bolded
query works fine, but the italicized query wont work because CTE does not exist.  How do I
create this?
CERTIFIED EXPERT

Commented:
have you checked my two comments:

http:#a37078741

and

http:#a37078777

?

Commented:
I just tried comment
1:
2:
3:
4:
5:
6:
7:
8:
9:

select CorrBillNo,
max(CopayList),
from (
select       distinct
p.CorrBillNo,
stuff((select ', ' + BillNo from TempTbl1 where CorrBillNo = p.corrBillNo for xml path('')) as CopayList
from TempTbl1 p
) t1
group by CorrBillNo

and get the following error :Incorrect syntax near the keyword 'from'.
I tried the select within the stuff and that said The multi-part identifier "p.corrBillNo" could not be bound. which I understand.
I tried the second suggestion on the message that suggests a max recursion of 10 and that gives me the error :       Incorrect syntax near the keyword 'from'.
which points to

Commented:
the' from tempTbl1 p' statement
CERTIFIED EXPERT

Commented:
extra comma there

select CorrBillNo,
max(CopayList)
from (
select       distinct
p.CorrBillNo,
stuff((select ', ' + BillNo from TempTbl1 where CorrBillNO = p.corrBillNo for xml path('')) as CopayList
from TempTbl1 p
) t1
group by CorrBillNo

Commented:
No, I had already solved the comma issue.  It is from the stuff statement which I have never used
and the from Error points to From TempTbl1 p   line.  Is there something wrong with the stuff line?
CERTIFIED EXPERT

Commented:
oops, stuff should be:

select CorrBillNo,
max(CopayList)
from (
select       distinct
p.CorrBillNo,
stuff((select ', ' + BillNo from TempTbl1 where CorrBillNO = p.corrBillNo for xml path('')), 1, 1, '') as CopayList
from TempTbl1 p
) t1
group by CorrBillNo

Commented:
Thanks for your assistance.
The code still doesn't work.  The error I got was: Conversion failed when converting the varchar value ', ' to data type int.

from select       distinct
p.CorrBillNo,
stuff((select ', ' + BillNo from TempTbl1 where CorrBillNO = p.corrBillNo for xml path('')), 1, 1, '') as CopayList
from TempTbl1 p
CERTIFIED EXPERT
Commented:
from select       distinct
p.CorrBillNo,
stuff((select ', ' + cast(BillNo as varchar(max)) from TempTbl1 where CorrBillNO = p.corrBillNo for xml path('')), 1, 1, '') as CopayList
from TempTbl1 p

Commented:
Thank you very much. The code I corrected worked, but I could not debug the
results until I used the stuff statement.  That showed numerous records that
had a Corresponding BillNO of Zero, so once I bypassed those records it flew.