Template example #

The example below creates a simple select command from metadata, i.e. it

• takes all column mappings to SELECT clause,
• all structured sources to FROM clause,
• it reflects Group by flag from column mappings and creates GROUP BY clause properly and
• it creates WHERE clause from Sources criteria

Template #

<#-- Source items to joins conversion ----------------------------------------------->
<#compress>  
<#assign sourcesSubst  = "">
<#if sourceItems?has_content>
    <#list sourceItems?filter(sourceItem -> 0 <= sourceItem.id) as sourceItem>
        <#if (sourceItem.stereotype!"")=="" || (sourceItem.objectType!"")=="" || ((sourceItem.objectCode!"")=="" && (sourceItem.objectCustom!"")=="" && ((sourceItem.objectType!"") != "DUMMY"))>
        <#lt>/*[ERROR:  Structured source item is incomplete! ("id": ${sourceItem.id})]*/
        </#if>
        <#if sourceItem.stereotype != "from">
            <#assign sourcesSubst = sourcesSubst+sourceItem.stereotype+" ">
        </#if>
        <#if sourceItem.objectType == "SUB_SELECT">
            <#assign sourcesSubst = sourcesSubst+"(\n"+sourceItem.objectCustom?replace('^',"            ",'rm')+"\n        ) ">
        <#elseif sourceItem.objectType != "DUMMY">
            <#assign sourcesSubst = sourcesSubst+sourceItem.ownerName+"."+sourceItem.objectCode+" ">
        </#if>
        <#if sourceItem.alias?has_content><#assign sourcesSubst  = sourcesSubst+sourceItem.alias+" "></#if> <#-- Do not use AS key word -->
            <#switch sourceItem.stereotype?replace("_"," ")>
                <#case "from">
                <#case "cross join">
                    <#assign sourcesSubst  = sourcesSubst+"\n">
                    <#break>
                <#case "join">
                <#case "inner join">
                <#case "left join">
                <#case "left outer join">
                <#case "right join">
                <#case "right outer join">
                <#case "full outer join">
                    <#assign sourcesSubst  = sourcesSubst+"\n        on "+sourceItem.expression?replace('\\n',"\n       ",'rm')+"\n">
                    <#break>
            </#switch>
    </#list>
</#if>
</#compress>
<#-- Main select ------------------------------------------------------------------------->
select
    <#list columns as column>
        <#lt><#assign mapFullStr=column.mapping+" as "+column.g_code>
        <#lt>${mapFullStr?replace('^',"        ",'rm')}<#if column?has_next>,</#if>
    </#list>
from
    <#lt>${sourcesSubst?replace('^',"        ",'rm')}
<#if criteria?has_content>where
    <#lt>${criteria?replace('^',"        ",'rm')}
</#if>
<#list columns?filter(col -> col.groupByFlag) as column>
<#if column?index == 0>
group by
</#if>
<#lt><#assign mapSubst = column.mapping><#rt>          
<#lt>${column.mapping?replace('^',"        ",'rm')}<#sep>,</#sep> -- as ${column.g_code}
</#list>
; 

How to try #

1. create a mapping
2. fill proper metadata in Column mappings and Sources tabs
3. in Settings, create a new Object Type and name it Pure Select
4. in Settings, create a new template and fill
   • Technology as technology of the system root of the template
   • Element type as Mapping
   • Stereotype as your mapping stereotype
   • Object type as Pure Select
5. add the object Pure Select in Overview tab of the mapping
6. go to Preview tab, select Pure Select from the drop-down and see the result