Link to home
Create AccountLog in
Avatar of 3M_chix
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,(length(e.group_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)='SINGAPORE' and f.type in ('INTERNAL'))
--   or
-- CONDITION B
   (substr(e.group_code,1,(length(e.group_code)-1)) = f.grp_code and upper(f.country)='SINGAPORE' 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,(length(e.group_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)='SINGAPORE' and f.type in ('INTERNAL'))
   --or
-- CONDITION B
   --(substr(e.group_code,1,(length(e.group_code)-1)) = f.grp_code and upper(f.country)='SINGAPORE' 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,(length(e.group_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)='SINGAPORE' and f.type in ('INTERNAL'))
   or
-- CONDITION B
   (substr(e.group_code,1,(length(e.group_code)-1)) = f.grp_code and upper(f.country)='SINGAPORE' and f.type in ('ARAPINTERNAL'))
   )

Can you pls assist with this?
Avatar of Ivo Stoykov
Ivo Stoykov
Flag of Bulgaria image

Hello 3M_chix,

try this

   and  
   (
-- CONDITION A
--   (e.group_code = f.grp_code  
--   or
-- CONDITION B
     substr(e.group_code,1,(length(e.group_code)-1)) = f.grp_code)
     and upper(f.country)='SINGAPORE' and f.type in ('INTERNAL', 'ARAPINTERNAL')
   )

Regards,

!I!
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of 3M_chix
3M_chix

ASKER

@ivostoykov:
I cannot use this because the f.grp_code condition differs between INTERNAL and ARAPINTERNAL f.type.
Avatar of 3M_chix

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 !!