Entering multiple values to ODBC sql WHERE clause as a single parameter

[dbig]

I have a workbook that pulls data from several oracle tables (same database) based on a common key value or values. Using the Query wizard, I build the core query ... "select * from Table1 where KEYVAL = 'C1234'". Next, I go into Data/Properties, then Connection Properties and convert the query to be parameter driven ... "select * from Table1 where KEYVAL = ?". Now, when I first run the modified query, excel asks me to locate the cell containing the KEYVAL value, e.g. A1. I can also check a box to have it always look in A1 for that parameter value.

My use case is that I want to run the retrieval query on multiple values at once. I can use the "in" operator, e.g. "select * from Table1 where KEYVAL in (?,?,?,?,?)" and supply a different cell for each "?" parameter (A1, A2, A3, A4, A5) and then check each Refresh checkbox, but I could have anywhere from 5 to 30+ parameters for any given refresh. Further, the same KEYVALs are used in an in-line subquery as well.

What I really want is to somehow feed the query via an excel Named Range, something like ...
"select * from Table1 where KEYVAL in (?)" or "select * from Table1 where instr(?, KEYVAL) >0" where the ? is fed the Named Range or a concatenated list of comma separated values.

I've tried both of these constructs without success. Each seems to accept the approach, but neither returns any data.

Anyone have any ideas? Thanks.

 

[Rijnsent]

Hi Dbig,
I've been testing a bit and found a working Instr version here: Passing Multiple Values to One Parameter – Bacon Bits
I've tested it on an Excel->Access link and it works like a charm, I have one parameter field with all my elements, just a space between them and they all get fetched nicely.
If that doesn't work, please post your SQL, maybe there's some other hiccup there.
Cheers,
Koen

 

[dbig]

Hi Dbig,
I've been testing a bit and found a working Instr version here: Passing Multiple Values to One Parameter – Bacon Bits
I've tested it on an Excel->Access link and it works like a charm, I have one parameter field with all my elements, just a space between them and they all get fetched nicely.
If that doesn't work, please post your SQL, maybe there's some other hiccup there.
Cheers,
Koen

Thanks Koen, That worked like a charm. What I learned was that the Named Range approach wouldn't work because still it contained multiple cells (... where the Parameter mechanics are expecting only a single cell). However, by concatenating the multiple inputs into a single string, that satisfies the single cell constraint. Now, using the "where instr( ?, p.PER_ID_NO ) >0" construct in the core query as well as all sub-queries works beautifully. The first time through, each instance of the where clause (in my case, four) will generate its own prompt which I point to the same cell and set to Refresh on Change. Viola! Now, I can drive the process for anywhere from one to ~fifty values, more than enough to cover my needs.

Secondarily, I want to complement you on your final caution ... to help, but not do the job. It's a point well made ... been there & had to deal with it.
Thanks, again.
Dave

 

[Delfinus25]

Is there a way to do this as a NOT IN clause? I tried using the same method, but changing the statement to <1, but that didn't seem to work. Unless my parameter string that's entered in the cell is possibly incorrect?

 

[Rijnsent]

Hi Delfinus25,
did you solve your issue? If not, could you please add some more details of the values in e.g. your cells/SQL? My guess is that it should work with any SQL clause, so a wrong string is my first suspect.
Cheers,
Koen