SqlDef-based
Where a more complex query spanning multiple models is needed to produce the interface data, or where an extra performance boost is needed beyond what the Model-based generator can provide, one can use a SqlDef-based processor.
In this case, one defines an arbitrary SQL query that includes macros for applying net change or sysId filtering as appropriate. For example, a query for an enterprise interface containing fields Name, Active and ValueChainId might be written as:
select ent_name,decode(nvl(is_active,1),0,'false',1,'true')is_active, vc_id || ''from enterprisewhere ent_name='ProgressiveRetailer'${filterIfNetChange: and last_modified_date>=$START_DATE$ and last_modified_date<=$END_DATE$}${filterIfSysId: and sys_ent_id IN $SYS_IDS$}The filterIfNetChange and filterIfSysId macros allow this query to be reused in both contexts. Note also that all values returned in the select clause must be VARCHARs, and they should be returned in the same order as the fields are defined in the interface.
When creating a SqlDef-based processor, you have the option of including a header row, as well as generating a zero-record file.
Include Header Row - Selecting this option will include the column attributes as a header row. This will provide context to what columns are included in the file. Deselecting this option will generate the file without a header row.
Generate Zero-Record File - Selecting this option will ensure that a file is generated even if there are no records within the file. Deselecting this option means that no file will be generated if there are no records within that file.
