StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOracle 'CONNECT BY' Syntax
text
Body
copied!<p>This is an offshoot of the following question: <a href="https://stackoverflow.com/questions/3524604/single-out-duplicates-between-two-result-sets">Single out duplicates between two result sets</a></p> <p>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.</p> <p>My query:</p> <pre><code>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)</code></pre> <p>Can anyone point me in the right direction to get it converted to the different syntax?</p> <p>I'm thinking something along the lines of:</p> <pre><code>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</code></pre> <p>This is not working obviously, and I don't know how to integrate the functionality of the fnpp...'s yet.Any help?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload