查询三级数据结构sql语句

with canshuji_data as(
SELECT A.INSTANCE_ID,instance_rrn
FROM NAMED_OBJECT A, PARAMETER_SET_EXT B
WHERE A.INSTANCE_RRN = B.PARAMETER_SET_RRN
AND OBJECT = ‘PARAMETERSET’
AND B.ATTRIBUTE_DATA4 != ‘TFI’
AND NAMED_SPACE = ‘MYCIM2’
),
canshu_data as(
SELECT distinct
noo.instance_id,
noo.instance_rrn ,
p.PARAMETER_SET_RRN
FROM PARAMETER_SET_VERSION p , named_object noo, PARAMETER pa,
(select instance_rrn，max(instance_version) instance_version
from OBJECT_VERSION t --where instance_rrn = 5658043
group by instance_rrn) t
WHERE --PARAMETER_SET_RRN = 5658043
–and
p.parameter_set_version = t.instance_version
and p.parameter_rrn = noo.instance_rrn
and pa.parameter_rrn = noo.instance_rrn
),
guize_data as （
SELECT
prr.PARAMETER_RRN,
pr.RULE_ID AS ruleId
FROM PARAMETER_RULE_RELATION prr
JOIN PARAMETER_RULE pr
ON prr.RULE_RRN = pr.RULE_RRN
–WHERE prr.PARAMETER_RRN = 5658039
)
select distinct c.*
from
canshuji_data c,
canshu_data c1,
guize_data g
where c.instance_rrn=c1.PARAMETER_SET_RRN(+)
and c1.instance_rrn=g.PARAMETER_RRN(+)
and g.PARAMETER_RRN is not null