Query to fetch additional fields configured in request templates

Query to fetch additional fields configured in request templates

Postgres:

SELECT ca.aliasname AS "UDF field Name",
ca.columnname AS "UDF Column Name",
ca.field_type AS "UDF Field Type",
sd.name AS "Service Category",
rtl.templatename AS "Template Name" FROM columnaliases ca
LEFT JOIN form_customizer fc ON ca.columnaliasesid::text = substring(fc.field_name,  position('_' IN fc.field_name)+1+ position('_' IN substring(fc.field_name,position('_' IN fc.field_name)+1,length(fc.field_name))) , length(fc.field_name))
LEFT JOIN requesttemplate_list rtl ON rtl.templatename = substring(fc.form_name,position('_' IN fc.form_name)+1,length(fc.form_name)) 
LEFT JOIN servicedefinition sd ON rtl.parent_service = sd.serviceid ORDER BY 1

MSSQL:

SELECT ca.aliasname "UDF field Name",
rtl.templateid as "Template ID",
rtl.templatename as "Template Name",
aau.first_name as "Template Owner",
rtl.is_catalog_template as "Service OR Incident template",
rtl.status as "template is active OR inactive?" FROM RequestTemplate_List rtl 
LEFT JOIN AaaUser aau ON rtl.createdby = aau.user_id
LEFT JOIN form_customizer fc on rtl.templatename = substring(fc.form_name, CHARINDEX('_', fc.form_name)+1 , len(fc.form_name) )
INNER JOIN columnaliases ca on cast(ca.columnaliasesid as varchar) = substring(fc.field_name, CHARINDEX('_', fc.field_name, CHARINDEX('_', fc.field_name)+1)+1 , len(fc.field_name)) ORDER BY 1‚Äč

                    New to ADSelfService Plus?