The Rittman Mead's Sql2Odi tool that converts SQL SELECT statements to ODI Mappings, now supports the SQL WITH statement as well. (For an overview of our tool's capabilities, please refer to our blog posts here and here.)
The Case for WITH Statements
If a SELECT statement is complex, in particular if it queries data from multiple source tables and relies on subqueries to do so, there is a good chance that rewriting it as a WITH statement will make it easier to read and understand. Let me show you what I mean...
SELECT LAST_NAME, FIRST_NAME, LAST_NAME || ' ' || FIRST_NAME AS FULL_NAME, AGE, COALESCE(LARGE_CITY.CITY, ALL_CITY.CITY) CITY, LARGE_CITY.POPULATION FROM ODI_DEMO.SRC_CUSTOMER CST INNER JOIN ODI_DEMO.SRC_CITY ALL_CITY ON ALL_CITY.CITY_ID = CST.CITY_ID LEFT JOIN ( SELECT CITY_ID, UPPER(CITY) CITY, POPULATION FROM ODI_DEMO.SRC_CITY WHERE POPULATION > 750000 ) LARGE_CITY ON LARGE_CITY.CITY_ID = CST.CITY_ID WHERE AGE BETWEEN 25 AND 45
This is an example from my original blog posts. Whilst one could argue that the query is not that complex, it does contain a subquery, which means that the query does not read nicely from top to bottom - you will likely need to look at the subquery first for the master query to make sense to you.
Same query, rewritten as a
WITH statement, looks like this:
WITH BASE AS ( SELECT LAST_NAME, FIRST_NAME, LAST_NAME || ' ' || FIRST_NAME AS FULL_NAME, AGE, CITY_ID FROM ODI_DEMO.SRC_CUSTOMER CST ), LARGE_CITY AS ( SELECT CITY_ID, UPPER(CITY) CITY, POPULATION FROM ODI_DEMO.SRC_CITY WHERE POPULATION > 750000 ), ALL_DATA AS ( SELECT LAST_NAME, FIRST_NAME, FULL_NAME, AGE, COALESCE(LARGE_CITY.CITY, ALL_CITY.CITY) CITY, LARGE_CITY.POPULATION FROM BASE CST INNER JOIN ODI_DEMO.SRC_CITY ALL_CITY ON ALL_CITY.CITY_ID = CST.CITY_ID LEFT JOIN LARGE_CITY ON LARGE_CITY.CITY_ID = CST.CITY_ID WHERE AGE BETWEEN 25 AND 45 ) SELECT * FROM ALL_DATA
Whilst it is longer, it reads nicely from top to bottom. And the more complex the query, the more the comprehensibility will matter.
The first version of our Sql2Odi tool did not support
WITH statements. But it does now.
Convert a WITH Statement to an ODI Mapping
The process is same old - first we add the two statements to our metadata table, add some additional data to it, like the ODI Project and Folder names, the name of the Mapping, the Target table that we want to populate and how to map the query result to the Target table, names of Knowledge Modules and their config, etc.
After running the Sql2Odi Parser, which now happily accepts
WITH statements, and the Sql2Odi ODI Content Generator, we end up with two mappings:
What do we see when we open the mappings?
SELECT statement based mappings is generated like this:
WITH statement mapping, though it queries the same data in pretty much the same way, is more verbose:
The additional EXPRESSION components are added to represent references to the
WITH subqueries. While the mapping is now busier than the original
SELECT, there should be no noticeable performance penalty. Both mappings generate the exact same output.