PDMListQuery

This template is the Cargo equivalent of Template:PDMListDPL and its related .dpl templates, as well as the specialized queries in Template:PDMIndexListDPL and on the Info Mart Partitioning page, providing the queries for various lists. The query requires an object to be specified, and supports a number of optional parameters that further filter or format the resulting lists:

• group
• withDesc
• format
• addFilter (currently in the where condition for tables and views only)

The field parameter for links to table and view pages is super-complicated, so as to allow for links to tables/views whose names end in an underscore but whose URLs don't, and the where criterion always splits for PSAAS vs. the other Reporting products.

Content is:

{{#switch:{{{object}}}
|Table=<!-- Lists of tables with shortdesc (in table format) and without shortdesc (in bullet format)-->
{{#switch:{{{withDesc}}}
|yes={{#css: th.field_Description {width:70%;} }}
{{#cargo_query:
tables=PDMTable
|fields=CONCAT('[[Documentation:{{#explode:{{{page}}}|:|1}}:{{#explode:{{{page}}}|:|2}}:Table-',IF(SUBSTRING(tableName,-1)="_",SUBSTRING(tableName,1,(LENGTH(tableName)-1)),tableName),':{{#explode:{{{page}}}|:|4}}|',REPLACE(tableName,"_","_<wbr />"),']]')=Table,shortDesc=Description
|where=_pageName LIKE {{#switch:{{#explode:{{{page}}}|:|1}}|PSAAS="Documentation:GIM:Library:Table-%%:{{#switch:{{#explode:{{{page}}}|:|4}}|draft=DRAFT|Public=PDMSource}}"|GIM|#default="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}}}}"}}{{#if:{{{group|}}}|
{{#switch:{{#explode:{{{page}}}|:|1}}|GIM|PSAAS={{#switch:{{{group}}}|fact= AND tableName LIKE "%_FACT%"|dimension= AND tableName NOT LIKE "%_FACT%" AND tableName NOT LIKE "CTL_%%" AND tableName NOT LIKE "STG_%%" AND tableName NOT LIKE "IRF_USER_DATA_%%_1"|dimensionalModel= AND tableName NOT LIKE "CTL_%%" AND tableName NOT LIKE "STG_%%"|control= AND tableName LIKE "CTL_%%"|staging= AND tableName LIKE "STG_%%"|#default=}}|ICON|#default= AND tableGroup LIKE "{{{group}}}"}}|}}{{#if:{{{addFilter|}}}|{{{addFilter}}}|}}
|limit=1000
|format=table
|named args=yes
}}
|no
|#default={{#cargo_query:
tables=PDMTable
|fields=CONCAT('[[Documentation:{{#explode:{{{page}}}|:|1}}:{{#explode:{{{page}}}|:|2}}:Table-',IF(SUBSTRING(tableName,-1)="_",SUBSTRING(tableName,1,(LENGTH(tableName)-1)),tableName),':{{#explode:{{{page}}}|:|4}}|',tableName,']]')
|where=_pageName LIKE {{#switch:{{#explode:{{{page}}}|:|1}}|PSAAS="Documentation:GIM:Library:Table-%%:{{#switch:{{#explode:{{{page}}}|:|4}}|draft=DRAFT|Public=PDMSource}}"|GIM|#default="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}}}}"}}{{#if:{{{group|}}}|
{{#switch:{{#explode:{{{page}}}|:|1}}|GIM|PSAAS={{#switch:{{{group}}}|fact= AND tableName LIKE "%_FACT%"|dimension= AND tableName NOT LIKE "%_FACT%" AND tableName NOT LIKE "CTL_%%" AND tableName NOT LIKE "STG_%%" AND tableName NOT LIKE "IRF_USER_DATA_%%_1"|dimensionalModel= AND tableName NOT LIKE "CTL_%%" AND tableName NOT LIKE "STG_%%"|control= AND tableName LIKE "CTL_%%"|staging= AND tableName LIKE "STG_%%"|#default=}}|ICON|#default= AND tableGroup LIKE "{{{group}}}"}}|}}{{#if:{{{addFilter|}}}|{{{addFilter}}}|}}
|limit=1000
|format=ul
}}
}}
|View=<!-- Lists of views with shortdesc (in table format) and without shortdesc (in bullet format)-->
{{#switch:{{{withDesc}}}
|yes={{#css: th.field_Description {width:70%;} }}
{{#cargo_query:
tables=PDMView
|fields=CONCAT('[[Documentation:{{#explode:{{{page}}}|:|1}}:{{#explode:{{{page}}}|:|2}}:View-',IF(SUBSTRING(viewName,-1)="_",SUBSTRING(viewName,1,(LENGTH(viewName)-1)),viewName),':{{#explode:{{{page}}}|:|4}}|',REPLACE(viewName,"_","_<wbr />"),']]')=View,shortDesc=Description
|where=_pageName LIKE {{#switch:{{#explode:{{{page}}}|:|1}}|PSAAS="Documentation:GIM:Library:View-%%:{{#switch:{{#explode:{{{page}}}|:|4}}|draft=DRAFT|Public=PDMSource}}"|GIM|#default="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 viewName NOT LIKE "CDR%%"{{#switch:{{{group}}}|admin= AND (viewName LIKE "Admin_%%" OR viewName LIKE "CTL_%%")|dimensionalModel= AND viewName NOT LIKE "Admin_%%" AND viewName NOT LIKE "CTL_%%"|#default=}}{{#if:{{{addFilter|}}}|{{{addFilter}}}|}}
|limit=1000
|format=table
|named args=yes
}}
|no
|#default={{#cargo_query:
tables=PDMView
|fields=CONCAT('[[Documentation:{{#explode:{{{page}}}|:|1}}:{{#explode:{{{page}}}|:|2}}:View-',IF(SUBSTRING(viewName,-1)="_",SUBSTRING(viewName,1,(LENGTH(viewName)-1)),viewName),':{{#explode:{{{page}}}|:|4}}|',viewName,']]')
|where=_pageName LIKE {{#switch:{{#explode:{{{page}}}|:|1}}|PSAAS="Documentation:GIM:Library:View-%%:{{#switch:{{#explode:{{{page}}}|:|4}}|draft=DRAFT|Public=PDMSource}}"|GIM|#default="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 viewName NOT LIKE "CDR%%"{{#switch:{{{group}}}|admin= AND (viewName LIKE "Admin_%%" OR viewName LIKE "CTL_%%")|dimensionalModel= AND viewName NOT LIKE "Admin_%%" AND viewName NOT LIKE "CTL_%%"|#default=}}{{#if:{{{addFilter|}}}|{{{addFilter}}}|}}
|limit=1000
|format=ul
}}
}}
|SubjArea=<!-- Lists of subject areas with shortdesc (in table format) and without shortdesc (in bullet format)-->
{{#switch:{{{withDesc}}}
|yes={{#cargo_query:
tables=PDMs_SubjectArea
|fields=CONCAT('[[Documentation:{{#explode:{{{page}}}|:|1}}:{{#explode:{{{page}}}|:|2}}:SubjArea-',IF(SUBSTRING(subjectArea,-1)="_",SUBSTRING(subjectArea,1,(LENGTH(subjectArea)-1)),subjectArea),':{{#explode:{{{page}}}|:|4}}|',subjectArea,']]')=Subject Area,shortDesc=Description
|where=_pageName LIKE {{#switch:{{#explode:{{{page}}}|:|1}}|PSAAS="Documentation:GIM:Library:SubjArea-%%:{{#switch:{{#explode:{{{page}}}|:|4}}|draft=DRAFT|Public=PDMSource}}"|GIM|#default="Documentation:{{#explode:{{{page}}}|:|1}}:Library:SubjArea-%%:{{#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}}}}"}}
|limit=1000
|format=table
|named args=yes
}}
|no
|#default={{#cargo_query:
tables=PDMs_SubjectArea
|fields=CONCAT('[[Documentation:{{#explode:{{{page}}}|:|1}}:{{#explode:{{{page}}}|:|2}}:SubjArea-',IF(SUBSTRING(subjectArea,-1)="_",SUBSTRING(subjectArea,1,(LENGTH(subjectArea)-1)),subjectArea),':{{#explode:{{{page}}}|:|4}}|',subjectArea,']]')
|where=_pageName LIKE {{#switch:{{#explode:{{{page}}}|:|1}}|PSAAS="Documentation:GIM:Library:SubjArea-%%:{{#switch:{{#explode:{{{page}}}|:|4}}|draft=DRAFT|Public=PDMSource}}"|GIM|#default="Documentation:{{#explode:{{{page}}}|:|1}}:Library:SubjArea-%%:{{#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}}}}"}}
|limit=1000
|format=ul
}}
}}
|Index=<!-- Lists of indexes in table format with shortdesc (used in GIM) and without shortdesc (used in ICON)-->
{{#css: td.field_U {text-align: center} td.field_C {text-align: center} }}
{{#cargo_query:
tables=PDMIndexItem,PDMTable
|join on=PDMIndexItem._pageName=PDMTable._pageName
|fields=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}}|',PDMTable.tableName,']]')=Table,PDMIndexItem.indexName=Index,IF( LCASE(PDMIndexItem.isUnique)="yes",CONCAT('X'),'')=U,{{#switch:{{#explode:{{{page}}}|:|1}}|ICON=IF( LCASE(PDMIndexItem.cluster)="yes",CONCAT('X'),'')=C|GIM|#default=PDMIndexItem.indexDesc=Description}}
|where=PDMIndexItem._pageName LIKE {{#switch:{{#explode:{{{page}}}|:|1}}|PSAAS="Documentation:GIM:Library:Table-%%:{{#switch:{{#explode:{{{page}}}|:|4}}|draft=DRAFT|Public=PDMSource}}"|GIM|#default="Documentation:{{#explode:{{{page}}}|:|1}}:Library:Table-%%:{{#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}}}}"}}
|format=dynamic table
|order by=PDMTable.tableName
|limit=5000
|rows per page=150
}}
|Schema=<!--List of ICON schemas with shortdesc (in no-border table format)-->
{{{!}} class="no-border"
{{#cargo_query:
tables=PDMSchema
|fields=_pageName=pageName,schemaName=Schema,shortDesc=Description
|where=_pageName LIKE "Documentation:ICON:Library:Schema-%:{{#switch:{{lc:{{#sub:{{{page}}}|-5}}}}|draft|ource={{#explode:{{{page}}}|:|4}}|#default={{#sub:{{#explode:{{{page}}}|:|4}}|0|3}}PDMSource}}"
|format=template
|template=PDMListFormat_TableRows
|named args=yes
|order by=priority
}}
{{!}}} <!--end of no-border table-->
}}