Untangling calculated fields (recursive SQL)
Do you remember a pain when calculated expression splits across dozens calculated fields?
That SQL will help you to visualise complex dependencies of calculated fields:
SELECT level,
SYS_CONNECT_BY_PATH(t.NAME, '/') PATH,
t.*
FROM
(SELECT f.NAME, f.CALCVAL
FROM siebel.S_FIELD f
JOIN siebel.S_BUSCOMP b ON b.row_id = f.BUSCOMP_ID
WHERE b.name = 'Order Entry - Orders' -- bus comp name
) t
START WITH name = 'Payment Order Total' -- field name
CONNECT BY prior CALCVAL LIKE '%[' || NAME || ']%'
ORDER BY 2;
It will show you all subsequent fields for vanilla calculated field [Payment Order Total] (BusComp = Order Entry - Orders) in recursion:
Here is a full version of SQL with more details
with t as (
select f.NAME, f.CALCVAL,
DECODE(f.MULTI_VALUED, 'Y', f.MVLINK_NAME || '.' || f.DEST_FLD_NAME,
DECODE(f.CALCULATED, 'Y', f.CALCVAL,
DECODE(f.JOIN_NAME, null, b.TABLE_NAME || '.' || f.COL_NAME,
DECODE(j.DEST_TBL_NAME, null, f.JOIN_NAME || '.' || f.COL_NAME,
j.DEST_TBL_NAME || '.' || f.COL_NAME)))) VAL
from siebel.S_FIELD f
join siebel.S_BUSCOMP b on b.row_id = f.BUSCOMP_ID
left join siebel.S_JOIN j on j.NAME = f.JOIN_NAME AND j.BUSCOMP_ID = f.BUSCOMP_ID
where b.name = 'Order Entry - Orders')
select distinct level, SYS_CONNECT_BY_PATH(t.NAME, '/') PATH, t.NAME, t.VAL from t
start with name = 'Payment Order Total'
connect by prior CALCVAL LIKE '%[' || NAME || ']%'
order by 2;