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

Hi P

Please don't cross post.

See a response on c.d.o.server.

Cheers

Richard
"P" <zarathustri_at_a.com> wrote in message news:sw3B9.76948$g9.217757_at_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 - 05:41:02 CST