Pass multiples values as single parameter – Oracle SQL query

July 15, 2022

I have this query that I’m passing 2 parameters, COUNTRY_REGION parameter and COST_CENTER parameter

I have the possibility to pass both parameters at the same time COST_CENTER and COUNTRY_REGION….. Or pass one or the other… This part is OK.

You can see in the first image below

SELECT
 dwg.GEOGRAPHY_ID         as geographyId
,INITCAP (lower (dwc.COUNTRY_REGION))       as countryRegion
,INITCAP (lower (dwc.COUNTRY_NAME))         as countryName
,dp.PROJECT_ID            as projectId
FROM 
DATALAKE.DWL_GEOGRAPHIES dwg
,DATALAKE.DWB_PROJECT dp
,DATALAKE.DWL_GEOGRAPHY_COUNTRIES dwgc
,DATALAKE.DWL_COUNTRY dwc
,DATALAKE.DWB_PROJECT_FINANCIAL dpf
,DATALAKE.DWL_GOLIVE dgl
where dwg.geography_id = dp.project_geography_id
and   dwg.geography_id = dwgc.geography_id
and   dwc.country_id   = dwgc.country_id
and   dp.PROJECT_ID =  dpf.PROJECT_ID
and   dpf.PROJECT_ID = dgl.PROJECT_ID
and   dpf.FLAG_ACTIVE = 1
and  ((dp.cost_center = (:costCenter) and INITCAP (lower (dwc.COUNTRY_REGION)) = (:countryRegion))
        or (:costCenter IS null and INITCAP (lower (dwc.COUNTRY_REGION)) = (:countryRegion))
        or (dp.cost_center = (:costCenter) and :countryRegion IS null)
     )
order by dwc.COUNTRY_REGION

Query result

But I WANT TO HAVE THE OPTION TO PASSA TWO OR MORE IN THE SAME PARAMETER as in the image below…

example:

COUNTRY_REGION: Peru, Chile, Argentina

COST_CENTER : 10500, 1000, … , ….

I would like help, I’ve tried several things and I can’t proceed, thank you very much.

>Solution :

You cannot pass multiple values with a single bind variable.

What you can do is pass in a single string that contains a delimited list and match a sub-string of the list to the value:

SELECT *
FROM   table_name
WHERE  ', ' || :country_region_list || ', ' LIKE '%, ' || country_region || ', %'
OR     ', ' || :cost_centre_list || ', ' LIKE '%, ' || cost_centre || ', %'

Which would make your query:

SELECT dwg.GEOGRAPHY_ID            as geographyId
     , INITCAP(dwc.COUNTRY_REGION) as countryRegion
     , INITCAP(dwc.COUNTRY_NAME)   as countryName
     , dp.PROJECT_ID               as projectId
FROM   DATALAKE.DWL_GEOGRAPHIES dwg
       INNER JOIN DATALAKE.DWB_PROJECT dp
       ON (dwg.geography_id = dp.project_geography_id)
       INNER JOIN DATALAKE.DWL_GEOGRAPHY_COUNTRIES dwgc
       ON (dwg.geography_id = dwgc.geography_id)
       INNER JOIN DATALAKE.DWL_COUNTRY dwc
       ON (dwc.country_id   = dwgc.country_id)
       INNER JOIN DATALAKE.DWB_PROJECT_FINANCIAL dpf
       ON (dp.PROJECT_ID =  dpf.PROJECT_ID)
       INNER JOIN DATALAKE.DWL_GOLIVE dgl
       ON (dpf.PROJECT_ID = dgl.PROJECT_ID)
WHERE  dpf.FLAG_ACTIVE = 1
AND    (
         ', ' || :costCenter || ', ' LIKE '%, ' || dp.cost_center || ', %'
       OR :costCenter IS null
       )
AND    (
         ', ' || :countryRegion || ', '
           LIKE '%, ' || INITCAP(dwc.COUNTRY_REGION) || ', %'
       OR :countryRegion IS null
       )
AND    (:costCenter IS NOT NULL OR :countryRegion IS NOT NULL)
order by dwc.COUNTRY_REGION