Oracle 'CONNECT BY' Syntax

Question

This is an offshoot of the following question: Single out duplicates between two result sets

As by a comment in that questions, I'm trying to implement my query using Oracle's special 'CONNECT BY' syntax. I'm having trouble finding any (clear) information on how to implement the syntax in my case.

My query:

SELECT pi.compressed_name, pi.phn, to_char(pi.date_of_birth , 'YYYY/MM/DD') as date_of_birth,
  to_char(pe.started_on , 'YYYY/MM/DD' ) as medicare_eligibility_start,
  to_char(pe.ended_on , 'YYYY/MM/DD' ) as medicare_eligibility_end
FROM    medcrtr.forest_node fnpppp,
    medcrtr.forest_node fnppp,
    medcrtr.forest_node fnpp,
    medcrtr.forest_node fnp,
    medcrtr.forest_node fn,
    medcrtr.group_member gm,
    medcrtr.program_eligibility pe,
    person_index pi
WHERE   gm.entity_type_id = 1
    AND fn.source_id = gm.group_id
    AND fn.entity_type_id = 3
    AND fnp.id = fn.parent_id
    AND fnpp.id = fnp.parent_id
    AND fnppp.id = fnpp.parent_id
    AND fnpppp.id = fnppp.parent_id
    AND pe.person_id = gm.source_id
    AND pe.sub_program_id = fnpp.parent_id
    AND pi.person_id = gm.source_id
  AND fnppp.id = 1169
    AND (gm.ended_on >= SYSDATE OR gm.ended_on IS NULL)

Can anyone point me in the right direction to get it converted to the different syntax?

I'm thinking something along the lines of:

SELECT   pi.compressed_name, pi.phn, to_char(pi.date_of_birth , 'YYYY/MM/DD') as date_of_birth,
    to_char(pe.started_on , 'YYYY/MM/DD' ) as medicare_eligibility_start,
    to_char(pe.ended_on , 'YYYY/MM/DD' ) as medicare_eligibility_end
FROM    medcrtr.forest_node fn,
    group_member gm,
    program_eligibility pe,
    person_index pi
WHERE   gm.entity_type_id = 1
    AND fn.source_id = gm.group_id
    AND fn.entity_type_id = 3
    AND pe.person_id = gm.source_id
    --AND pe.sub_program_id = fnpp.parent_id ???
    AND pi.person_id = gm.source_id
    --AND fnppp.id = 1169 ???
    AND (gm.ended_on >= SYSDATE OR gm.ended_on IS NULL)
CONNECT BY PRIOR fn.id=fn.parent_id

This is not working obviously, and I don't know how to integrate the functionality of the fnpp...'s yet.Any help?

Answer 1

I'm not sure but I think you're missing the start with clause:

SELECT   pi.compressed_name, pi.phn, to_char(pi.date_of_birth , 'YYYY/MM/DD') as date_of_birth,
    to_char(pe.started_on , 'YYYY/MM/DD' ) as medicare_eligibility_start,
    to_char(pe.ended_on , 'YYYY/MM/DD' ) as medicare_eligibility_end
FROM    medcrtr.forest_node fn,
    group_member gm,
    program_eligibility pe,
    person_index pi
WHERE   gm.entity_type_id = 1
    AND fn.source_id = gm.group_id
    AND fn.entity_type_id = 3
    AND pe.person_id = gm.source_id
    AND pi.person_id = gm.source_id
    AND (gm.ended_on >= SYSDATE OR gm.ended_on IS NULL)
start with fn.id = 1169 /*THis is where the recursion will start */
CONNECT BY PRIOR prior fn.id=fn.parent_id /*specify you want the current node's parent id to match the previous node's id*/

A good reference: http://www.adp-gmbh.ch/ora/sql/connect_by.html