CODEWITHSUNDEEP
oracle-databaseloops
Gangs165700
Gangs165700

Reputation: 131

Iterate a select query for a set of varchar2 in oracle

I have a below select query

    select * from BUSINESS_T
where  store_code = '075'
and item_no in
(
         select item_no from BUSINESS_T a
         where store_code = '075'
         and exists
         (
                  select * from BUSINESS_T
                  where store_code = a.store_code 
                  and item_no = a.item_no
                  and
                  (
                            VALID_FROM_DTIME between a.VALID_FROM_DTIME and a.VALID_TO_DTIME
                            or VALID_TO_DTIME between a.VALID_FROM_DTIME and a.VALID_TO_DTIME
                            or (VALID_FROM_DTIME > a.VALID_FROM_DTIME and a.VALID_TO_DTIME is null)
                            or (VALID_FROM_DTIME < a.VALID_FROM_DTIME and VALID_TO_DTIME is null)
                  )
                  and del_dtime is null
                  and not
                  (
                            a.rowid = rowid
                  )
         )
)
order by item_no, VALID_FROM_DTIME

Need to run it for a array of store numbers {'071','072','073','074','075','076'} This array should defined inside the query itself.

Nearly 400+ fixed store numbers are there. The above query has to be run for each store, at a time for one store , To find the overlapping in that particular store

Upvotes: 0

Views: 103

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

If i run by passing the collection of store numbers, there is a chance items are common in many stores that will cause a problem.

You can still use in if you modify the first subquery to get return the store/item pairs, which handles the common items:

select * from BUSINESS_T
where (store_code, item_no) in
(
         select store_code, item_no from BUSINESS_T a
         where store_code in ('071','072','073','074','075','076')
...

Or with a collection:

select * from BUSINESS_T
where (store_code, item_no) in
(
         select store_code, item_no from BUSINESS_T a
         where store_code member of sys.dbms_debug_vc2coll('071','072','073','074','075','076')
...

db<>fiddle with very simple demo of the idea.

Upvotes: 1

GMB
GMB

Reputation: 222482

Use in:

select *
from business_t
where 
    class_unit_code in ('071', '072', '073', '074', '075', '076')
    and b_type = 'CASH_AND_CARRY' 
    and delete_date is null

For this specific sequence of string values, we might try to shorten the predicate using a regex (although this is probably less efficient):

regexp_like(class_unit_code, '^07[1-6]$')

Or if the string always contains numeric values, we can convert and use a range comparison (which also is not as efficient as the first option - in that case, the column should have been created with a numeric datatype to start with):

to_number(class_unit_code) between 71 and 76

Upvotes: 1

Related Questions