Excel parameterized SQL query with IN clause

[dotsent]

Hi!

I'm running Office365 package Excel 2016 with MS Query to DB2 SQL database with the query being parameterized towards a specific Excel cell value (although I need to populate this cell from a larger range first). While SQL clauses work fine when cell has a single value, I need to run SQL IN clause for multitude of values (can be 50-100).

The regular query outside of Excel would look something like this and works fine:

select * from products where products.id IN ('A', 'C', 'F')

Excel parameterized query with IN clause would look like:

select * from products where products.id IN ?

with the parameter linked to cell A1 holding value A - this IS working. It won't work anymore if cell A1 has value A, C providing error message:

"Bad parameter type. Microsoft Excel is expecting a different kind of value than what was provided"

I have already understood that running a parameterized query with multitude of values can be tricky, however there must be a workaround, right? Maybe my approach has to be somehow different here? All the values are also not originally in cell A1, but distributed over a range and I'm merging/concatting them together.

Using a parameter for each potential cell with individual value looks to work with small sample size, however requires insane amount of parameters to really set up (and test).

Please help with any ideas!

 

[John_w]

You would get that error if the JoinValues2 function is not in a standard module. It should also be available as a UDF: type =JoinValues2 in a cell to confirm this.

 

[ssjody1960]

I fixed my previous problem. My fault. The code I had in module1 wasn't what John had posted. I don't get the error now but I still get no results when the sql query is ran.

 

[ssjody1960]

I did confirm that the UDF is working by typing in =JoinValues2. It worked

 

[John_w]

I don't get the error now but I still get no results when the sql query is ran.

If you have created the SQL query correctly then you should be able to refresh it manually and it will return results.

The Worksheet_Change code simply changes the query's command text (SQL SELECT statement), specifically the "IN (xxxx)" clause, as per the requirement in the original post. You can see the current command text in the query's connection definition.

 

[ssjody1960]

Maybe this is where my failure is and maybe I'm missing a detail on how I should structure the command_text in my connection. In the command text of the connection I'm using for the query I have the following

select * from "CPIC"."OEMNUMTOCPCODE" where OEMNUM IN ('')

. Maybe I should have mentioned to that the connection is to an ORACLE database table. Don't know if that has anything to do with the syntax of the command_text.

 

[John_w]

I don't know about SQL Server or Oracle syntax, but try changing it to:

select * from "CPIC"."OEMNUMTOCPCODE" where CPIC.OEMNUM IN ('1234')

where 1234 is a valid string. I would also try omitting the double quotes: CPIC.OEMNUMTOCPCODE

However the apostrophes in '1234' suggests a string, not a number, so maybe this IN clause wouldn't work anyway if OEMNUM is a numeric column. Maybe IN (1,2,3,4) would work for you.