Search code examples
sqloracleconnect-by

Oracle SQL Recursive Query When FieldType is Subpage

I have a table PanelDefn which has list following fields 

PanelID  FieldID  RecordName FieldName LableText Subpanel  FieldType
PRV1     1        REC1       FLD1      Name                 1
PRV1     2        REC1       FLD2      Address              1 
PRV1     3        REC1       FLD3      Email                1
PRV1     4                                       SUB_PRV1   11
PRV1     5                                       SUB_PRV2   11
SUB_PRV1 1        REC2      FLD1       Address1             1
SUB_PRV1 2        REC2      FLD2       Address2             1
SUB_PRV1 3        REC2      FLD3       City                 1
SUB_PRV1 4        REC2      FLD4       Postal               2
SUB_PRV2 1        REC3      FLD1       EmailTYpe            1
SUB_PRV2 2        REC3      FLD2       PrimaryFlag          1

I would like to write a query which recursively gives the data for SubPanel when I query for Panel. It means whenever we encounter FieldType=11 , we need to have recursive query for that. I tried using connect-by.

Solution

  • I guess you need below query -

    SELECT *
FROM DATA
START WITH PanelID = 'PRV1'
CONNECT BY PRIOR Subpanel = PanelID

    This will recursively generate the records by comparing PanelID and Subpanel and then using START WITH clause will filter out the unnecessary records.