Comma seperated parameters to pass to a database stage

[kwwilliams]

Our business has many international sites, each site has multiple facilities. My requirement is to run a job for each site after midnight at each site to deliver a static picture of data at approximately midnight for each site. Because the logic for each site is the same, I was hoping to be able to create one job and use parameters to differentiate the connection and facilities. The connection information works fine, but I am struggling with the facilities.

I have created a parameter called facility. In my oracle stage I have user-defined sql that user the facility parameter like this:

where facility_dwid in (select facility_dwid from facility where facility_id in ('#FACILITY#')

This works fine if I use one facility, but I was hoping to be able to pass in a list that looked like this 'FACILITY 1','FACILITY 2','FACILITY 3'

That should make the sql physically translate to where facility_dwid in (select facility_dwid from facility where facility_id in ('FACILITY 1','FACILITY 2','FACILITY 3').

Thanks in advance,

[kumar_s]

Hi,

You can have an option by cancatinating all the value like 'Facility 1','Facility 2'... as and when required to a single variable before passing it.

Or You can pass many parameters initially maximum possible prameters, and pass the value to required parameter and make the rest of the parametes as blank.

-Kumar

[nick.bond]

From looking at your example I think you are doing the correct thing you just have the syntax wrong.

If you have "where facility_id in ('#pFACILITY#')"

and pass #pFACILITY# = 'FACILIY1', 'FACILITY2', 'FACILITY3'

your sql will have too many quotes.

i.e. it will look like "where facility_id in ('''FACILIY1', 'FACILITY2', 'FACILITY3'')"

If the string that is in your parameter has the ' in it try leaving them out of the sql clause. I haven't checked this becasue it is friday afternoon but have a go.

[kwwilliams]

That was just the latest variation that I had tried. Originally I had it just as you suggested. DataStage strips of the single quotes and passes just the string values with commas in between. Oracle then screams about incorrect syntax.

Kumar's suggestion worked where I have several parameters and pass them in leaving some blank, but fills a bit clunky to me. I was hoping to be able to pass one string, but will settle with what feels like a less elogant approach if that is all I have avaialble to me.