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

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  August 20, 2006 at 11:19 am

  #655802

  I just posted a similar solution at the following URL

  http://www.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)