We help IT Professionals succeed at work.

SQl Recursive problem

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

Author

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
HainKurtSr. 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) ),
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
is p.BillNO varchar? if not cast that one to varchar too...
HainKurtSr. 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

Author

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?
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)

Open in new window


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

Open in new window

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

Open in new window

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

Open in new window

Author

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

?

Author

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

Author

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

Author

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

Author

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

Author

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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.