Notes on Query transform
Ohh! My god what a transformation it is?… mind blowing… astonishing… This is one of the best best best transformations in DI, I would compare this component with a beautiful women in this world.
The beauty of this transformation is, it will try to push the code to the database as much as it can. By pushing down the code to the database, obviously reduces the engine overhead, thus improves performance.
Ex: You applied an aggregation function, then It says “Ok, If these function(s) are available in database, then let me push this operation to the database”
Technical Manuals says: A Query transform is similar to a SQL Select statement that retrieves a data set that satisfies conditions that are specified.
Here, I listed out Query transform functionalities, it has around 20 different functionalities.
Let’s discuss about those functionalities one by one,
- can map columns from in schema to out schema (Mapping tab), Just drag the required columns from in-schema to out-schema.
- can select the distinct records (to fetch only unique records) by checking Distinct rows option in Select tab.
- can filter out the data (using where clause conditions)
- can join tables (All kind of joins)
- can sort the records (using Order by tab)
- can combine/group the result set when you use aggregation functions (using Group by tab)
- can use available functions in DI (Aggregation, Conversion, Custom, Database, Date, Lookup, Math, Misc, String, Validation etc)
- can create New Output Schema
- can create a New output column
- can set a Primary key (it’s a toggle option) to a column
- can create a flat file format definition
- can generate a DTD format
- can generate an XSD schema definition
- can select a nested schema using “Make Current”
- can call functions(conversion, lookup, custom, stored procedures, adapter functions) thru “New function call”
- Unnest a nested schema (it’s a toggle option)
- Cut, copy, paste, delete of schemas, columns etc
- Search and Replace of column mapping
- Schema Remapping
All in all, this transform works as a SQL “SELECT statement” with extra features.
Ohh…forgot to mention in the above list, from 11.7 onwards there is another tab called Advanced (to create a separate process of GROUP BY, ORDER BY clauses etc.)
Hope I listed out almost all common options available in QRY transform, If I missed out anything then please do let me know…