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.

 

[sureshpaul]

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.

The function is not at all needed. The original error is coming because the string is very long to be handled by excel as a parameter. I had faced similar issue with microsoft query in excel with postgresql query
When you TEXTJOIN in excel make sure you join less number of cells to make it a string less than 250 characters. Then create such multiple cells. for e.g. My parameter column in excel contains around 1000 rows each cell having a code of 4 to 8 characters. Then I'm text joining as below on to another sheet
formula in cell Sheet3!A1 -> "=TEXTJOIN(",",TRUE,cpsCodes!A2:A30)"
formula in cell Sheet3!A2 -> "=TEXTJOIN(",",TRUE,cpsCodes!A31:A60)"
formula in cell Sheet3!A3 -> "=TEXTJOIN(",",TRUE,cpsCodes!A61:A90)"
formula in cell Sheet3!A4 -> "=TEXTJOIN(",",TRUE,cpsCodes!A91:A120)"
formula in cell Sheet3!A5 -> "=TEXTJOIN(",",TRUE,cpsCodes!A121:A150)"
etc...

My excel looks like below

and my codes looks like below with around 1000 rows in that and allows user to add more

Then initially my parameters will be hardcoded using value clause and joined to the query as below

FROM stock s
left join lateral (
select cmirrors, cvanity, vall, vcps, cpsa1, cpsa2, cpsa3, cpsa4, cpsa5
from (
values(
false
,false
,false
,true
,'3637,820105'
,'3638,820106'
,'3639,820107'
,'8100,820109'
,'8103,820113'
)
) a(
cmirrors, cvanity, vall, vcps, cpsa1, cpsa2, cpsa3, cpsa4, cpsa5
)
) params on true

which after the params are incorporated will looks like below

FROM stock s
left join lateral (
select cmirrors, cvanity, vall, vcps, cpsa1, cpsa2, cpsa3, cpsa4, cpsa5
from (
values(
?::boolean
,?::boolean
,?::boolean
,?::boolean
,?
,?
,?
,?
,?
)
) a(
cmirrors, cvanity, vall, vcps, cpsa1, cpsa2, cpsa3, cpsa4, cpsa5
)
) params on true

When used this way we can use the same parameter in multiple places in the query. that is the purpose of using that in this way. If you are using SQL server then instead of lateral join, it should be outer apply/cross apply and the SQL server has similar functions.

and the paramters for large strings should be in another sheet (or where ever convenient ) will be like below

If excel still give parameter type error, reduce the range to make the string smaller

The where clause looks something like below

and
(
(params.cmirrors = true and left(s.product_group,1) = 'B' and s.desc1 ilike '%mirror%')
or (params.cvanity = true and left(s.product_group,1) = 'B' and u.userc2 ilike '%vanity%')
or (params.vall = true and left(s.product_group,1) = 'A')
or (params.vcps = true and left(s.product_group,1) = 'A' and s.part in (select unnest(string_to_array(
concat(params.cpsa1
,case when params.cpsa2 <> '' then ',' else '' end,params.cpsa2
,case when params.cpsa3 <> '' then ',' else '' end,params.cpsa3
,case when params.cpsa4 <> '' then ',' else '' end,params.cpsa4
,case when params.cpsa5 <> '' then ',' else '' end,params.cpsa5
),','))))
)

This approach is working perfectly for me.

Also quite easy for the user with the easy parameter handling using the check boxes and list of codes