Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Running into a strange issue where a synthetic key is being created on a hierarchy table.
My main hierarchy table is HIERARCHY_ALL.
I'm seeing a synthetic key created on "HIERARHCY_ALL_TEMP-1" - not sure how this table is being created.
.qvs file below and screenshot attached.
Gerry.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//-----------------------------------------------------------------------//
// Description: Table HIERARCHY
//-----------------------------------------------------------------------//
TRACE >> Loading table HIERARCHY_ALL;
[HIERARCHY_ALL_TEMP]:
Hierarchy(ChildID, ParentID, Location, ParentLocation, Location, Path, \, NodeDepth)
load
HIERARCHY_CATEGORY & '-' & HIERARCHY_NAME & '-' & NODE_NAME & '-' & CONTEXT_ID as ChildID,
HIERARCHY_CATEGORY & '-' & HIERARCHY_NAME & '-' & PARENT_NODE & '-' & CONTEXT_ID as ParentID,
NODE_NAME as Location,
HIERARCHY_CATEGORY,
HIERARCHY_NAME,
CONTEXT_ID as HR_CONTEXT_ID,
TABLE_NAME & '-' & CONTRACT_REFERENCE & '-' & CONTEXT_ID as HIERARCHY_KEY
FROM [$(Parameters_Path_Qvd_Data)\HIERARCHY_ALL*.qvd](qvd)
where match(CONTEXT_ID, $(v_context_keys))>0
;
For v_level = 1 to 15
If FieldNumber('Location$(v_level)','HIERARCHY_ALL_TEMP') = 0 then
concatenate (HIERARCHY_ALL_TEMP)
Load null() as Location$(v_level)
autogenerate 0;
ENDIF
Next
[HIERARCHY_ALL]:
NoConcatenate
load * ,
if(IsNull(Location15),
if(IsNull(Location14),
if(IsNull(Location13),
if(IsNull(Location12),
if(IsNull(Location11),
if(IsNull(Location10),
if(IsNull(Location9),
if(IsNull(Location8),
if(IsNull(Location7),
if(IsNull(Location6),
if(IsNull(Location5),
if(IsNull(Location4),
if(IsNull(Location3),
if(IsNull(Location2),
(
Location1
),
(
Location1 & '|' &
Location2
)
),
(
Location1 & '|' &
Location2 & '|' &
Location3
)
),
(
Location1 & '|' &
Location2 & '|' &
Location3 & '|' &
Location4
)
),
(
Location1 & '|' &
Location2 & '|' &
Location3 & '|' &
Location4 & '|' &
Location5
)
),
(
Location1 & '|' &
Location2 & '|' &
Location3 & '|' &
Location4 & '|' &
Location5 & '|' &
Location6
)
),
(
Location1 & '|' &
Location2 & '|' &
Location3 & '|' &
Location4 & '|' &
Location5 & '|' &
Location6 & '|' &
Location7
)
),
(
Location1 & '|' &
Location2 & '|' &
Location3 & '|' &
Location4 & '|' &
Location5 & '|' &
Location6 & '|' &
Location7 & '|' &
Location8
)
),
(
Location1 & '|' &
Location2 & '|' &
Location3 & '|' &
Location4 & '|' &
Location5 & '|' &
Location6 & '|' &
Location7 & '|' &
Location8 & '|' &
Location9
)
),
(
Location1 & '|' &
Location2 & '|' &
Location3 & '|' &
Location4 & '|' &
Location5 & '|' &
Location6 & '|' &
Location7 & '|' &
Location8 & '|' &
Location9 & '|' &
Location10
)
),
(
Location1 & '|' &
Location2 & '|' &
Location3 & '|' &
Location4 & '|' &
Location5 & '|' &
Location6 & '|' &
Location7 & '|' &
Location8 & '|' &
Location9 & '|' &
Location10 & '|' &
Location11
)
),
(
Location1 & '|' &
Location2 & '|' &
Location3 & '|' &
Location4 & '|' &
Location5 & '|' &
Location6 & '|' &
Location7 & '|' &
Location8 & '|' &
Location9 & '|' &
Location10 & '|' &
Location11 & '|' &
Location12
)
),
(
Location1 & '|' &
Location2 & '|' &
Location3 & '|' &
Location4 & '|' &
Location5 & '|' &
Location6 & '|' &
Location7 & '|' &
Location8 & '|' &
Location9 & '|' &
Location10 & '|' &
Location11 & '|' &
Location13
)
),
(
Location1 & '|' &
Location2 & '|' &
Location3 & '|' &
Location4 & '|' &
Location5 & '|' &
Location6 & '|' &
Location7 & '|' &
Location8 & '|' &
Location9 & '|' &
Location10 & '|' &
Location11 & '|' &
Location14
)
),
(
Location1 & '|' &
Location2 & '|' &
Location3 & '|' &
Location4 & '|' &
Location5 & '|' &
Location6 & '|' &
Location7 & '|' &
Location8 & '|' &
Location9 & '|' &
Location10 & '|' &
Location11 & '|' &
Location14 & '|' &
Location15
)) as hierarchy_tree resident HIERARCHY_ALL_TEMP;
drop table HIERARCHY_ALL_TEMP;
TRACE >> Load table HIERARCHY_ALL done;
