3M_chix
asked on
SQL "OR" statement not working in Oracle 10.2.0.2
I'm encountering below issue executing the following statement in Oracle SQL. I'm not sure if this is a bug or not, but this used to work before.
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
When commenting CONDITION A, it is returning 11340 rows.
select /*+ INDEX (ITEM itemixc) */
d.local_acc_no as Account,
e.group_code,
f.grp_code,
f.country,
f.type,
substr(e.group_code,1,(len gth(e.grou p_code)-1) ),
a.short_code,
b.short_no
from lsub a,item b, db_ssrreport_date c, bank d,acgr e ,db_ssr_trpsetup f
where b.corr_acc_no = d.corr_acc_no
and a.corr_acc_no = b.corr_acc_no
and d.corr_acc_no = e.corr_acc_no and b.flag_2 = 0
and (b.value_date <= last_day(add_months(c.rpt_ dte,-1)) )
and claim_flg != 1
and
(
-- CONDITION A
-- (e.group_code = f.grp_code and upper(f.country)='SINGAPOR E' and f.type in ('INTERNAL'))
-- or
-- CONDITION B
(substr(e.group_code,1,(le ngth(e.gro up_code)-1 )) = f.grp_code and upper(f.country)='SINGAPOR E' and f.type in ('ARAPINTERNAL'))
)
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
When commenting CONDITION B, it is returning 88 rows.
select /*+ INDEX (ITEM itemixc) */
d.local_acc_no as Account,
e.group_code,
f.grp_code,
f.country,
f.type,
substr(e.group_code,1,(len gth(e.grou p_code)-1) ),
a.short_code,
b.short_no
from lsub a,item b, db_ssrreport_date c, bank d,acgr e ,db_ssr_trpsetup f
where b.corr_acc_no = d.corr_acc_no
and a.corr_acc_no = b.corr_acc_no
and d.corr_acc_no = e.corr_acc_no and b.flag_2 = 0
and (b.value_date <= last_day(add_months(c.rpt_ dte,-1)) )
and claim_flg != 1
and
(
-- CONDITION A
(e.group_code = f.grp_code and upper(f.country)='SINGAPOR E' and f.type in ('INTERNAL'))
--or
-- CONDITION B
--(substr(e.group_code,1,( length(e.g roup_code) -1)) = f.grp_code and upper(f.country)='SINGAPOR E' and f.type in ('ARAPINTERNAL'))
)
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
But when I use both conditions using "OR", logically it should return 11428 rows, but it is actually returning 499282 rows (!!)
The rows that are not supposed to be picked up also gets picked up.
select /*+ INDEX (ITEM itemixc) */
d.local_acc_no as Account,
e.group_code,
f.grp_code,
f.country,
f.type,
substr(e.group_code,1,(len gth(e.grou p_code)-1) ),
a.short_code,
b.short_no
from lsub a,item b, db_ssrreport_date c, bank d,acgr e ,db_ssr_trpsetup f
where b.corr_acc_no = d.corr_acc_no
and a.corr_acc_no = b.corr_acc_no
and d.corr_acc_no = e.corr_acc_no and b.flag_2 = 0
and (b.value_date <= last_day(add_months(c.rpt_ dte,-1)) )
and claim_flg != 1
and
(
-- CONDITION A
(e.group_code = f.grp_code and upper(f.country)='SINGAPOR E' and f.type in ('INTERNAL'))
or
-- CONDITION B
(substr(e.group_code,1,(le ngth(e.gro up_code)-1 )) = f.grp_code and upper(f.country)='SINGAPOR E' and f.type in ('ARAPINTERNAL'))
)
Can you pls assist with this?
--------------------------
When commenting CONDITION A, it is returning 11340 rows.
select /*+ INDEX (ITEM itemixc) */
d.local_acc_no as Account,
e.group_code,
f.grp_code,
f.country,
f.type,
substr(e.group_code,1,(len
a.short_code,
b.short_no
from lsub a,item b, db_ssrreport_date c, bank d,acgr e ,db_ssr_trpsetup f
where b.corr_acc_no = d.corr_acc_no
and a.corr_acc_no = b.corr_acc_no
and d.corr_acc_no = e.corr_acc_no and b.flag_2 = 0
and (b.value_date <= last_day(add_months(c.rpt_
and claim_flg != 1
and
(
-- CONDITION A
-- (e.group_code = f.grp_code and upper(f.country)='SINGAPOR
-- or
-- CONDITION B
(substr(e.group_code,1,(le
)
--------------------------
When commenting CONDITION B, it is returning 88 rows.
select /*+ INDEX (ITEM itemixc) */
d.local_acc_no as Account,
e.group_code,
f.grp_code,
f.country,
f.type,
substr(e.group_code,1,(len
a.short_code,
b.short_no
from lsub a,item b, db_ssrreport_date c, bank d,acgr e ,db_ssr_trpsetup f
where b.corr_acc_no = d.corr_acc_no
and a.corr_acc_no = b.corr_acc_no
and d.corr_acc_no = e.corr_acc_no and b.flag_2 = 0
and (b.value_date <= last_day(add_months(c.rpt_
and claim_flg != 1
and
(
-- CONDITION A
(e.group_code = f.grp_code and upper(f.country)='SINGAPOR
--or
-- CONDITION B
--(substr(e.group_code,1,(
)
--------------------------
But when I use both conditions using "OR", logically it should return 11428 rows, but it is actually returning 499282 rows (!!)
The rows that are not supposed to be picked up also gets picked up.
select /*+ INDEX (ITEM itemixc) */
d.local_acc_no as Account,
e.group_code,
f.grp_code,
f.country,
f.type,
substr(e.group_code,1,(len
a.short_code,
b.short_no
from lsub a,item b, db_ssrreport_date c, bank d,acgr e ,db_ssr_trpsetup f
where b.corr_acc_no = d.corr_acc_no
and a.corr_acc_no = b.corr_acc_no
and d.corr_acc_no = e.corr_acc_no and b.flag_2 = 0
and (b.value_date <= last_day(add_months(c.rpt_
and claim_flg != 1
and
(
-- CONDITION A
(e.group_code = f.grp_code and upper(f.country)='SINGAPOR
or
-- CONDITION B
(substr(e.group_code,1,(le
)
Can you pls assist with this?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
@ivostoykov:
I cannot use this because the f.grp_code condition differs between INTERNAL and ARAPINTERNAL f.type.
I cannot use this because the f.grp_code condition differs between INTERNAL and ARAPINTERNAL f.type.
ASKER
@angelIII:
Your SQL syntax seems to work fine. The claim_flg belongs to item b table so I've moved it to the correspoding JOIN condition accordingly. I have no clear idea where the additional rows would come from either. I am also raising an SR in Oracle Metalink to see if this is an issue on oracle 10g (since it was working OK in oracle 9i).
I am accepting your reply as a solution. Thanks a lot !!
Your SQL syntax seems to work fine. The claim_flg belongs to item b table so I've moved it to the correspoding JOIN condition accordingly. I have no clear idea where the additional rows would come from either. I am also raising an SR in Oracle Metalink to see if this is an issue on oracle 10g (since it was working OK in oracle 9i).
I am accepting your reply as a solution. Thanks a lot !!
try this
and
(
-- CONDITION A
-- (e.group_code = f.grp_code
-- or
-- CONDITION B
substr(e.group_code,1,(len
and upper(f.country)='SINGAPOR
)
Regards,
!I!