Oracle"CONNECT BY"语法 [英] Oracle 'CONNECT BY' Syntax

问题描述

这是以下问题的分支: 在两个结果集中选出重复项

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

正如对该问题的评论一样，我正在尝试使用Oracle特殊的"CONNECT BY"语法来实现我的查询.我在查找有关如何实现我的语法的任何(清晰的)信息时遇到了麻烦.

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.

我的查询:

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

这显然行不通，而且我还不知道如何集成fnpp的功能...有帮助吗?

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

推荐答案

我不确定，但我认为您缺少start with子句:

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*/

良好参考: http://www.adp-gmbh.ch /ora/sql/connect_by.html

这篇关于Oracle"CONNECT BY"语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！