select recname from pskeydefn where recname in (
select recname from pskeydefn where fieldname in
( select fieldname from pskeydefn
where recname = 'PARENT_RECORD_NAME' and indexid='_')
and indexid='_'
group by recname having count(fieldname) = (select count(fieldname)
from pskeydefn where recname='PARENT_RECORD_NAME' and indexid='_')
and sum(keyposn) = (select sum(keyposn) from pskeydefn
where recname='PARENT_RECORD_NAME' and indexid='_'))
group by recname having count(fieldname) > (select count(fieldname)
from pskeydefn where recname = 'PARENT_RECORD_NAME' and indexid='_')
another way of determining parent child relationship in peoplesoft. Good for analysis during data conversion to understand the data flow.
select P.RECNAME AS PARENT_RECORD ,C.RECNAME AS CHILD_RECORD,P.KEYSTRING AS PARENT_KEY ,c.KEYSTRING AS CHILD_KEY from (select RECNAME,max(ltrim(sys_connect_by_path(FIELDNAME,','),',')) keystring
from (select RECNAME,FIELDNAME,
row_number() over(partition by RECNAME order by KEYPOSN) rn
from PSKEYDEFN where indexid='_')
start with rn = 1
connect by prior rn = rn -1
and prior RECNAME = RECNAME
group by RECNAME) P ,(select RECNAME,max(ltrim(sys_connect_by_path(FIELDNAME,','),',')) keystring
from (select RECNAME,FIELDNAME,
row_number() over(partition by RECNAME order by KEYPOSN) rn
from PSKEYDEFN where indexid='_')
start with rn = 1
connect by prior rn = rn -1
and prior RECNAME = RECNAME
group by RECNAME) C
where instr(c.keystring,P.keystring)<>0
and p.recname=''PARENT_RECORD_NAME'
Another variation , includes descr of child record
Select t.PARENT_RECORD ,t.CHILD_RECORD,r.recdescr,t.PARENT_KEY,t.CHILD_KEY from psrecdefn r ,(
select P.RECNAME AS PARENT_RECORD ,C.RECNAME AS CHILD_RECORD,P.KEYSTRING AS PARENT_KEY ,c.KEYSTRING AS CHILD_KEY
from (select RECNAME,max(ltrim(sys_connect_by_path(FIELDNAME,','),',')) keystring
from (select RECNAME,FIELDNAME,
row_number() over(partition by RECNAME order by KEYPOSN) rn
from PSKEYDEFN where indexid='_')
start with rn = 1
connect by prior rn = rn -1
and prior RECNAME = RECNAME
group by RECNAME) P ,(select RECNAME,max(ltrim(sys_connect_by_path(FIELDNAME,','),',')) keystring
from (select RECNAME,FIELDNAME,
row_number() over(partition by RECNAME order by KEYPOSN) rn
from PSKEYDEFN where indexid='_')
start with rn = 1
connect by prior rn = rn -1
and prior RECNAME = RECNAME
group by RECNAME) C
where instr(c.keystring,P.keystring)<>0
and p.recname='VENDOR'
) t
where r.recname= t.CHILD_RECORD
new version with hierarchy.
select level, PARENT_RECORD, ltrim(sys_connect_by_path(child_record, '->'), '->') as child_record_tree
from (select P.RECNAME AS PARENT_RECORD, C.RECNAME AS CHILD_RECORD
from (select RECNAME,
max(ltrim(sys_connect_by_path(FIELDNAME, ','), ',')) keystring
from (select RECNAME,
FIELDNAME,
row_number() over(partition by RECNAME order by KEYPOSN) rn
from PSKEYDEFN
where indexid = '_')
start with rn = 1
connect by prior rn = rn - 1
and prior RECNAME = RECNAME
group by RECNAME) P,
(select RECNAME,
max(ltrim(sys_connect_by_path(FIELDNAME, ','), ',')) keystring
from (select RECNAME,
FIELDNAME,
row_number() over(partition by RECNAME order by KEYPOSN) rn
from PSKEYDEFN
where indexid = '_')
start with rn = 1
connect by prior rn = rn - 1
and prior RECNAME = RECNAME
group by RECNAME) C
where instr(c.keystring, P.keystring) <> 0) PC
connect by nocycle prior CHILD_RECORD = PARENT_RECORD
start with PARENT_RECORD = 'VENDOR'
joining with record description.
SELECT P.RECNAME AS PARENT_RECORD ,
C.RECNAME AS CHILD_RECORD,
P.KEYSTRING AS PARENT_KEY ,
c.KEYSTRING AS CHILD_KEY,
r1.recdescr as child_rec_descr, r1.descrlong as child_rec_descr_long
FROM
(SELECT RECNAME,
MAX(ltrim(sys_connect_by_path(FIELDNAME,','),',')) keystring
FROM
(SELECT RECNAME,
FIELDNAME,
row_number() over(partition BY RECNAME order by KEYPOSN) rn
FROM PSKEYDEFN
WHERE indexid='_'
)
START WITH rn = 1
CONNECT BY prior rn = rn -1
AND prior RECNAME = RECNAME
GROUP BY RECNAME
) P ,
(SELECT RECNAME,
MAX(ltrim(sys_connect_by_path(FIELDNAME,','),',')) keystring
FROM
(SELECT RECNAME,
FIELDNAME,
row_number() over(partition BY RECNAME order by KEYPOSN) rn
FROM PSKEYDEFN
WHERE indexid='_' and (recname not like '%AET' or recname not like '%TAO')
)
START WITH rn = 1
CONNECT BY prior rn = rn -1
AND prior RECNAME = RECNAME
GROUP BY RECNAME
) C, PSRECDEFN r1
WHERE instr(c.keystring,P.keystring)<>0
AND p.recname ='CA_DETAIL'
and c.recname=r1.recname
Hi Rahul.
Can you please update your script for SQL server and for PT 8.52 as your scripts are not executing….
Thanks,
Amar