Oracle SQL Recursive Query When FieldType is Subpage

Question

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.

Please show us the result that you expect for this sample data. — GMB, Apr 19 '20 at 13:49
This should be the output. In my where clause I would use " PanelID = 'PRV1' " and the output should be above result. Is this possible ? — Rohit Prasad, Apr 19 '20 at 13:52
Your question would be easier to understand if you could separate the sample data from the expected results. So you would have two different tables in the question, one for the sample data and the other for the expected results. — GMB, Apr 19 '20 at 14:05

score 1 · Accepted Answer · answered Apr 19 '20 at 14:30

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.

Oracle SQL Recursive Query When FieldType is Subpage

1 Answers1