Search code examples
oracleouter-joinansi-sql

Oracle Plus (+) Joins to ANSI conversion

I'm in the middle of a warehouse migration from Oracle to SQL Datawarehouse Azure and ran into an issue with this query.

The original query from Oracle - it returns 1872520 rows. 

SELECT
 *
FROM
 STG_REV_APPORTION_CSC_NO t1,
 STG_SEP_VL t2,
 STG_SEP_VL t3
WHERE
 t3.BUSINESS_DATE(+)    = t1.BUSINESS_DATE
AND t3.CSC_APP_NO(+)     = t1.CSC_APP_NO
AND t3.JOURNEY_NO(+)     = t1.JOURNEY_NO
AND t3.PURSE_TXN_CTR(+)  = t1.PURSE_TXN_CTR
AND t2.BUSINESS_DATE(+) = t1.BUSINESS_DATE
AND t2.CSC_APP_NO(+)    = t1.CSC_APP_NO
AND t2.JOURNEY_NO(+)    = t1.JOURNEY_NO
AND
 (
   t2.TRIP_NO(+) + 1
 )
 = t1.TRIP_NO
AND
 (
   t2.MSG_TYPE_CD(+)  = 13070
 AND t3.MSG_TYPE_CD(+) = 4357
 );

Taking clues from documentation, I tried query re-write to ANSI:

SELECT COUNT(*) 
 FROM STG_REV_APPORTION_CSC_NO t1
 RIGHT OUTER JOIN STG_SEP_VL t3 ON t3.BUSINESS_DATE = t1.BUSINESS_DATE
 AND t3.CSC_APP_NO     = t1.CSC_APP_NO
 AND t3.JOURNEY_NO     = t1.JOURNEY_NO
 AND t3.PURSE_TXN_CTR  = t1.PURSE_TXN_CTR
 RIGHT OUTER JOIN STG_SEP_VL t2 ON t2.BUSINESS_DATE = t1.BUSINESS_DATE
AND t2.CSC_APP_NO   = t1.CSC_APP_NO
AND t2.JOURNEY_NO    = t1.JOURNEY_NO 
AND (t2.TRIP_NO + 1)  = t1.TRIP_NO
WHERE t2.MSG_TYPE_CD = 13070 AND t3.MSG_TYPE_CD = 4357

It returns zero rows. The ANSI version should work on oracle instance - it returns zero rows there too.

I then tried to convert plus join to ANSI using refactor option on toad. I get the following 

SELECT *
  FROM STG_SEP_VL  T2
       RIGHT OUTER JOIN STG_REV_APPORTION_CSC_NO T1
           ON     (T2.BUSINESS_DATE = T1.BUSINESS_DATE)
              AND (T2.CSC_APP_NO = T1.CSC_APP_NO)
              AND (T2.JOURNEY_NO = T1.JOURNEY_NO)
       RIGHT OUTER JOIN STG_SEP_VL T3
           ON     (T3.PURSE_TXN_CTR = T1.PURSE_TXN_CTR)
              AND (T3.BUSINESS_DATE = T1.BUSINESS_DATE)
              AND (T3.CSC_APP_NO = T1.CSC_APP_NO)
              AND (T3.JOURNEY_NO = T1.JOURNEY_NO)
 WHERE     ( ( (T2.TRIP_NO                                             /*(+)*/
                          ) + 1) = T1.TRIP_NO)
       AND ( ( (T2.MSG_TYPE_CD                                         /*(+)*/
                              ) = 13070) AND ( (T3.MSG_TYPE_CD         /*(+)*/
                                                              ) = 4357));

Now this query should run on Oracle and return the same number of rows before I can run it on SQL Server. But it doesn't - it returns zero rows.

I looked at the explain plan for both of these queries. Here is how (+) join plan looks like: enter image description here

Here is how ANSI version of this query looks like: enter image description here

Am I missing something?

Solution

  • Here's what I came up with:

    SELECT *
  FROM stg_rev_apportion_csc_no t1 
  LEFT JOIN stg_sep_vl t3
         ON t1.business_date = t3.business_date AND
            t1.csc_app_no = t3.csc_app_no       AND
            t1.journey_no = t3.journey_no       AND
            t1.purse_txn_ctr = t3.purse_txn_no  AND
            4357 = t3.msg_type_cd
  LEFT JOIN stg_sep_vl t2
         ON t1.business_date = t2.business_date AND
            t1.csc_app_no = t2.csc_app_no       AND
            t1.journey_no = t2.journey_no       AND
            t1.trip_no = t2.trip_no + 1         AND
            13070 = t2.msg_type_cd;

    Tables t2 and t3 are outer joined to t1, so you either list t1 first and do a left join, or list t2 and t3 first and do a right join.