PDMRefQuery

This template is the Cargo equivalent of Template:PDMRefDPL, providing the query to populate the table of references on ICON Schema and GIM Library Table and View pages, and Template:PDMRefListDPL, providing the query to populate the Reference List page in GIM. It is called by Template:PDMSchemaPageFormat, Template:PDMTable and Template:PDMTablePageFormat,Template:PDMViewPageFormat, and Template:PDMPageQuery. There are separate queries for ICON and GIM. Note that, while RAA does capture PDMRef information, none of this information displays in the RAA PDMs.

The where criterion for the GIM ref list is super-complicated, so as to allow for links to tables/views whose names end in an underscore but whose URLs don't, as well as to exclude certain tables/views from the PSAAS page.

Content is:

{{#switch:{{#explode:{{{page}}}|:|1}}
|ICON={{#cargo_query:
tables=PDMRef,PDMTable
|join on=PDMRef._pageName=PDMTable._pageName
|fields=CONCAT('[[{{#replace:{{{page}}}|{{#explode:{{{page}}}|:|3}}|Table-',PDMTable.tableName,'}}|',PDMTable.tableName,']]')=Child Table,PDMRef.parentTable=Parent Table,PDMRef.refCode=CODE,PDMRef.foreignKey=F
|where=PDMRef._pageName LIKE "Documentation:ICON:Library:%%:{{#switch:{{lc:{{#sub:{{{page}}}|-5}}}}|draft|ource={{#explode:{{{page}}}|:|4}}|#default={{#switch:{{lc:{{#explode:{{{page}}}|:|4}}}}|public|current=PDMSource|#default={{#sub:{{#explode:{{{page}}}|:|4}}|0|3}}PDMSource}}}}" AND PDMTable.dbSchema LIKE "{{#explode:{{#explode:{{{page}}}|:|3}}|-|1}}"
|format=sortable table
|default=No references defined.
}}
|PSAAS
|GIM=<!-- GIM needs to switch for Table/View pages vs. the Reference list -->
{{#switch:{{#explode:{{#explode:{{{page}}}|:|3}}|-|0}}
|Table
|View={{#cargo_query:
tables=PDMRef,PDMTable,PDMView
|join on=PDMRef._pageName=PDMTable._pageName,PDMRef._pageName=PDMView._pageName
|fields=IF( (PDMRef._pageName LIKE "%:Table-%"),PDMTable.tableName,PDMView.viewName)=Child Table/View,PDMRef.foreignKey=Child Table/View Column,IF( (PDMRef.parentTable IS NOT NULL),PDMRef.parentTable,PDMRef.parentView)=Parent Table/View,PDMRef.parentColumn=Parent Table/View Column
|where=PDMRef._pageName LIKE "{{{page}}}"
|default=There are no references from this table/view.
|format=sortable table
}}
|InfoMartReferences
|#default={{#cargo_query:
tables=PDMRef,PDMTable,PDMView
|join on=PDMRef._pageName=PDMTable._pageName,PDMRef._pageName=PDMView._pageName
|fields=IF( (PDMTable.tableName IS NOT NULL),CONCAT('[[Documentation:{{#explode:{{{page}}}|:|1}}:{{#explode:{{{page}}}|:|2}}:Table-',IF(SUBSTRING(PDMTable.tableName,-1)="_",SUBSTRING(PDMTable.tableName,1,(LENGTH(PDMTable.tableName)-1)),PDMTable.tableName),':{{#explode:{{{page}}}|:|4}}|',REPLACE(PDMTable.tableName,"_","_<wbr/>"),']]'),
CONCAT('[[Documentation:{{#explode:{{{page}}}|:|1}}:{{#explode:{{{page}}}|:|2}}:View-',IF(SUBSTRING(PDMView.viewName,-1)="_",SUBSTRING(PDMView.viewName,1,(LENGTH(PDMView.viewName)-1)),PDMView.viewName),':{{#explode:{{{page}}}|:|4}}|',REPLACE(PDMView.viewName,"_","_<wbr/>"),']]'))=ChildTable/ChildView,PDMRef.foreignKey=ChildTable/ChildView Column,IF( (PDMRef.parentTable IS NOT NULL),PDMRef.parentTable,PDMRef.parentView)=ParentTable/ParentView,IF( (PDMRef.parentColumn IS NOT NULL),PDMRef.parentColumn,'')=ParentTable/ParentView Column
|where={{#if:{{{source|}}}|PDMRef._pageName LIKE "{{{source}}}"|{{#switch:{{#explode:{{{page}}}|:|1}}|PSAAS=PDMRef._pageName LIKE "Documentation:GIM:Library:%%:{{#switch:{{#explode:{{{page}}}|:|4}}|draft=DRAFT|Public=PDMSource}}" AND (PDMRef._pageName NOT LIKE "%CTL_%" OR PDMRef._pageName NOT LIKE "%STG_%" OR PDMRef._pageName NOT LIKE "%View-CDR%")|#default=PDMRef._pageName LIKE "Documentation:{{#explode:{{{page}}}|:|1}}:Library:%%:{{#switch:{{lc:{{#sub:{{{page}}}|-5}}}}|draft|ource={{#explode:{{{page}}}|:|4}}|#default={{#switch:{{lc:{{#explode:{{{page}}}|:|4}}}}|public|current=PDMSource|#default={{#sub:{{#explode:{{{page}}}|:|4}}|0|3}}PDMSource}}}}" AND PDMRef._pageName NOT LIKE "%View-CDR%"}}}}
|format=dynamic table
|order by=ChildTable/ChildView
|rows per page=150
|limit=5000
}}
}}
|#default=
}}