INSERT INTO c (
SELECT
dozen_columns
FROM A
LEFT OUTER JOIN B ON A.the_id = B.the_id
WHERE B.a_column IS NULL
OR A.scd_1_hash <> B.scd_1_hash
OR A.scd_2_hash <> B.scd_2_hash
);
It seems to me that DI does not distinguish between JOIN conditions and other filter conditions, and that the generated SQL takes advantage of Oracle’s (almost) deprecated syntax by using “(+)” -notation which probably prevents adding the filters. I’m new to DI but I’d like to see a different tab for WHERE and JOIN clauses and maybe an option to use ANSI JOIN syntax?
What would be the best way to do the insert? The source tables are not that big (both < 200k rows) so the operation can be done in DI. On the other hand, the filtering condition is quite restrictive so it would be nice to use it before loading all the data into DI (row amount should drop to less than 10k per load). For now, I use a SQL transformation to get the data set in, then bulk load it (unaltered) back to database.
Yes, that’s what I want as well, a seperate tab for joins. You can still place the join condition in the where-tab but in the join tab you would have more options.
While I agree that there should be an option for ANSI SQL, I don’t know where you get the information that old Oracle join syntax is to be deprecated any time soon. Since that would mean removing the support for natural joins and probably forcing code updates to a good percentage of it’s customer base, I just don’t see it happening.
As to how to do it, the dataflow would be simple enough:
TBL1 -\
---> QRY1 ---> TBL3
TBL2 -/
In QRY1, go into the Outer Join tab an set up the relationship. In the where clause, specify the conditions you want. Save the dataflow. Go to Validattion | Display Optimized SQL and check that it meets your needs.
If for some reason it does not, you can simply add a PUSHDOWN_SQL statement to force the where clause to perform as needed.
Depending upon the version of DI you’re using, it should push down the WHERE clause filters into the FROM join clause – but only on DI 11.7.2 or above. (At least with SQL Server, this functionality, when combined with an OR clause as you have, does not work in 11.7.0. I don’t know about Oracle.)
My memory was failing me with the “(+)” -syntax, Oracle only recommends in SQL Reference: “Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator.” This is due to some restrictions with Oracle syntax.
I’m using DI 11.7.2.2. If I interpret the DI Reference correctly, outer joins for Oracle use Oracle syntax, not ANSI JOIN syntax. For SQL Server and DB2 the WHERE clause join conditions are pushed to FROM clause.
If I do the mapping in the way Ernie suggested without the pushdown_sql, both tables are loaded to DI, joined and wrong result set is inserted to target table as if the filter is not applied at all (I guess I must have being doing something wrong there). pushdown_sql cannot be used if the outer join is defined.
However, if I do not define the outer join in DI and instead use pushdown_sql and define the outer join there (using Oracle syntax) and modify the where clause correctly, then the whole mapping is pushed down to database just the way I like! DI only warns me that all the tables are not joined, since it does not parse the pushdown_sql contents.
So, the final (anonymized) WHERE clause is: pushdown_sql(‘MyDataSource’, ‘A.THE_ID = B.THE_ID (+) AND (B.a_column IS NULL OR A.SCD_1_HASH <> B.SCD_1_HASH OR A.SCD_2_HASH <> B.SCD_2_HASH)’)
(BOB member since 2008-01-03)
(BOB member since 2004-12-17)
(BOB member since 2005-09-16)