Query returns different rows with new syntax and old (+) syntax

From: P <zarathustri_at_a.com>
Date: Fri, 15 Nov 2002 21:04:38 +1100
Message-ID: <sw3B9.76948$g9.217757@newsfeeds.bigpond.com>

Hi,

I need some help. A developer wrote this query based on the new 9i left outer join
syntax, and now realizes that this is to run on an earlier version of Oracle with the
(+) syntax. We tried rewrting the query, but it is returning a different row set:

Any help appreciated. Please reply to steven.yee_at_team.telstra.com Thanks in advance.

Regards,
Steven

SQL Statement :
SELECT A.ACCT_NUM,

       AT.OTHER_NAMES,
       AT.LAST_NAME,
       EM.ACCOUNT_NUM,
       AT.USER_ID,
       B2.PASSWORD,
       B2.IS_PRIMARY,
       UT1.TIER_CODE,
       P.USAGEALLOWED

FROM BIDUSERS B1
JOIN ACCOUNTS A ON (A.ACCT_NUM = B1.ACCT_NUM) JOIN ACCT_TAB AT ON (AT.ACCTTELS = RTRIM(B1.ACCT_NUM)) LEFT OUTER JOIN EMAIL_NOTIFICATION_DISABLED EM ON (EM.ACCOUNT_NUM = RTRIM(B1.ACCT_NUM))
JOIN BIDUSERS B2 ON (B2.LOGIN_ID = RPAD(AT.USER_ID, 8) AND B2.ACCT_NUM = B1.ACCT_NUM)
LEFT OUTER JOIN USERTIER UT1 ON (UT1.LOGIN_ID = RPAD(AT.USER_ID, 8) AND UT1.TIER_CODE = 'adm')
LEFT OUTER JOIN USERTIER UT2 ON (UT2.LOGIN_ID = RPAD(AT.USER_ID, 8) AND B2.IS_PRIMARY = 'Y')
LEFT OUTER JOIN PLAN P ON (P.TIER_CODE = RTRIM(UT2.TIER_CODE)) WHERE B1.LOGIN_ID = 'backlash'
AND B1.IS_SUBSCRIBER = 'Y'
AND B1.LOGIN_ALLOWED = 'Y'
AND A.ACCT_STATUS = 1
AND (P.TIER_CODE IS NOT NULL OR B2.IS_PRIMARY = 'N') ORDER BY B2.IS_PRIMARY DESC This returns the following rows:

ACCT_NUM             OTHER_NAMES                      LAST_NAME
ACCOUNT_NUM          USER_ID                          PASSWORD IS_PRIMARY
TIER_CODE USAGEALLOWED


"1052155             " "STEVEN"                         "YEE"

"1052155"            "backlash"                       "crap    " "Y"

"adm     " 10000

"1052155             " "STEVEN"                         "YEE"

"1052155"            "sifu"                           "crap    " "N"

"adm     "

"1052155             " "STEVEN"                         "YEE"

"1052155"            "mryoda"                         "crap    " "N"

"adm     "

"1052155             " "STEVEN"                         "YEE"

"1052155"            "freefall"                       "crap    " "N"

"adm     "

The rewritten query:

SQL Statement :
SELECT A.ACCT_NUM,

       AT.OTHER_NAMES,
       AT.LAST_NAME,
       EM.ACCOUNT_NUM,
       AT.USER_ID,
       B2.PASSWORD,
       B2.IS_PRIMARY,
       UT1.TIER_CODE,
       P.USAGEALLOWED
FROM BIDUSERS B1,
     ACCOUNTS A,
     ACCT_TAB AT,
     EMAIL_NOTIFICATION_DISABLED EM,
     BIDUSERS B2,
     USERTIER UT1,
     USERTIER UT2,
     PLAN P

WHERE A.ACCT_NUM = B1.ACCT_NUM
AND AT.ACCTTELS = RTRIM(B1.ACCT_NUM)
AND RTRIM(B1.ACCT_NUM) = EM.ACCOUNT_NUM(+)

AND   (B2.LOGIN_ID = RPAD(AT.USER_ID, 8) AND B2.ACCT_NUM = B1.ACCT_NUM)
AND   (RPAD(AT.USER_ID, 8)=UT1.LOGIN_ID(+) AND UT1.TIER_CODE = 'adm')
AND   (RPAD(AT.USER_ID, 8)=UT2.LOGIN_ID(+) AND B2.IS_PRIMARY = 'Y')

AND RTRIM(UT2.TIER_CODE) = P.TIER_CODE(+)

AND   B1.LOGIN_ID = 'backlash'
AND   B1.IS_SUBSCRIBER = 'Y'
AND   B1.LOGIN_ALLOWED = 'Y'

AND A.ACCT_STATUS = 1
AND (P.TIER_CODE IS NOT NULL OR B2.IS_PRIMARY = 'N') ORDER BY B2.IS_PRIMARY DESC This returns lesser rows:

ACCT_NUM             OTHER_NAMES                      LAST_NAME
ACCOUNT_NUM          USER_ID                          PASSWORD IS_PRIMARY
TIER_CODE USAGEALLOWED


"1052155             " "STEVEN"                         "YEE"

"1052155"            "backlash"                       "crap    " "Y"

"adm     " 10000

Received on Fri Nov 15 2002 - 04:04:38 CST