0

I have a query working that allows the user to select by date range, store (one store number) and zip code,

Regarding store I want to be able to enter multiple store numbers separated by commas.

The code below works for a single store but not for multiple store numbers

      SELECT tt.id_str_rt store
           ,SUBSTR(tt.inf_ct,1,5) zip_code
           ,COUNT(tt.ai_trn) tran_count
           ,SUM(tr.mo_nt_tot) sales_value
          FROM orco_owner.tr_trn tt
           ,orco_owner.tr_rtl tr
         WHERE tt.id_str_rt = tr.id_str_rt


          AND (tt.id_str_rt IN NVL(:PM_store_number,tt.id_str_rt) OR :PM_store_number IS NULL)


           AND NVL(SUBSTR(tt.inf_ct,1,5),0) = NVL(:PM_zip_code,NVL(SUBSTR(tt.inf_ct,1,5),0))
           AND tt.id_ws = tr.id_ws
           AND tt.dc_dy_bsn = tr.dc_dy_bsn
           AND tt.ai_trn = tr.ai_trn
                  AND TRUNC(TO_DATE(tt.dc_dy_bsn,'yyyy-MM-dd'))
          BETWEEN NVL(:PM_date_from, TRUNC(TO_DATE(tt.dc_dy_bsn,'yyyy-MM-dd')))
              AND NVL(:PM_date_to,TRUNC(TO_DATE(tt.dc_dy_bsn,'yyyy-MM-dd')))
           AND LENGTH(TRIM(TRANSLATE(SUBSTR(inf_ct,1,5), '0123456789', ' '))) IS NULL
          GROUP BY tt.id_str_rt,SUBSTR(tt.inf_ct,1,5)
          ORDER BY zip_code, store
John T
  • 1
  • 7

1 Answers1

0

You could create a function like the one described in how to convert csv to table in oracle:

create or replace function splitter(p_str in varchar2) return  sys.odcivarchar2list
is
v_tab sys.odcivarchar2list:=new sys.odcivarchar2list();
begin
with cte as (select level  ind from dual
connect by 
level <=regexp_count(p_str,',') +1
)
select regexp_substr(p_str,'[^,]+',1,ind)
bulk collect into v_tab
from cte;
return v_tab;
end;
/

Then you would use it in your query like this:

and (tt.id_str_rt in (select column_value from table(splitter(:PM_store_number)) ))

instead of this:

AND (tt.id_str_rt IN NVL(:PM_store_number,tt.id_str_rt) OR :PM_store_number IS NULL)
Community
  • 1
  • 1
RFerreira
  • 1
  • 3