If you have ever needed to hierarchical extract data from an 8i system to put in a data integrator template for import into a TRIRIGA 9 system, you probably know about the challenge of pulling the parent path. The following SQL statement can be used to extract this data (this example is for Space Class Current):
select a.spec_id, a.sys_parent1, a.classname,
(select b.fullpath from tridata.t_spaceclasscurrent b where b.spec_id = a.sys_parent1) "PARENTPATH"
from tridata.t_spaceclasscurrent a
order by a.fullpath
Subscribe to:
Post Comments (Atom)
1 comment:
Here is another query to pull path from the IBS_SPEC table, adding it to fields from the T table (this example is for floors):
select
a.LOCID, a.LOCNAME, a.LEVEL1, a.GROSSAREANUM, a.GROSSMAREANUM, a.status,
(select I.OBJECT_PATH from tridata.ibs_spec i where a.sys_parent1 = i.spec_id) "PARENTPATH"
from tridata.T_FLOOR a
where a.status = 'Active'
order by a.sys_parent1
Post a Comment