What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

-- Download Query Transform as PDF --

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.

QRY Mapping

  • can select the distinct records (to fetch only unique records) by checking Distinct rows option in Select tab.

QRY Distinct

  • can filter out the data (using where clause conditions)
  • can join tables (All kind of joins)
Run_tot Where
In Outer Join tab, mention inner source and outer source Where clause will treat as outer join. You can view Optimized SQL Go back to DF designer window, click on Validation Menu–>Display Optimized SQL

  • can sort the records (using Order by tab)
Run_tot Order by
  • can combine/group the result set when you use aggregation functions (using Group by tab)
Run_tot Group by
  • 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…

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

2 comments on “Query Transform

    • Full Outer Join:
      step1) Take src1 & src2, do left outer join in 1 qry transform and keep it aside
      step2) take another query called qry2, now flip the sources in left outer join clause which becomes right outer join (manage src2,src1 in left outer clause).
      step3) Now merge the both query resultants(left outer, right outer resultants) using merge join and then add a query to apply distinct results and then place target objects.

      Sub Query: Lookup is one kind of subquery. Query after a Query transform can also be used and check the code thru optimized sql option once you develop a DF with proper sub query(s).

      Hope it helps!

      Sudheer Sharma

Leave a Reply

Your email address will not be published. Required fields are marked *