Recursive CTE is not recursive

Question

Post reply

Recursive CTE is not recursive

Gabor Nyul

SSCrazy Eights

Points: 9459
More actions
August 18, 2006 at 8:42 am

#92367

Hi there

This query sould produce a single row result. instead of this I have a 3 row result (ie it seems it is not iterating, just looping throu all records

with ip (SYSTEM_ID, IP_ADDRESS) as

     (

     select distinct IP0.SYSTEM_ID,

            cast('' as varchar(400))

     from   CEPPLUS.IP_INTERFACE IP0

     where IP0.SYSTEM_ID = 188

     union all

     select IP1.SYSTEM_ID,

          IP1.IP_ADDRESS + case when IP1.IP_ADDRESS = '' then '' else ', ' end + IP2.IP_ADDRESS

     from   ip IP1, CEPPLUS.IP_INTERFACE IP2

     where IP1.SYSTEM_ID = IP2.SYSTEM_ID

     and    IP1.IP_ADDRESS = ''

     )

select * from ip where IP_ADDRESS != ''

Here is the result:

SYSTEM_ID IP_ADDRESS

188  , 10.168.4.182

188  , 62.184.239.71

188  , 10.168.2.79

instead of the desired

SYSTEM_ID IP_ADDRESS

188  10.168.4.182, 62.184.239.71, 10.168.2.79

Bye
Gabor

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

I just posted a similar solution at the following URL

http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=302356

look for the function I created...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions