PocketQuery - how to pass parameters using openquery

Todd Winton

In our environment, we have SQL and AS400.  We currently access AS400 data in SQL using open query.  Is there a way to add parameters for openquery?

Example:

 

select * from openquery

(as400prod, 'select mdsccd as Scac, count (mdsccd) as total from mbdtlpf where mdcucd in (''0937'') and mdentd = ''2015-02-23'' group by mdsccd')

 

Here I would want mdcucd  and mdentd  to be parameters

Felix Grund (Scandio)

Hi Todd!

Thank you for your interest in PocketQuery. In general, you'll be able to write any SQL your databases understands in the PQ admin interface. I think the problem in your case is that you want your parameter between single quotes. In some version in the past, we stopped interpreting the parameters between single quotes, because there were some SQL statements that required colons in between.

However, there should be an easy workaround: you could use your the concatenate function of your SQL dialect. We've had this in another thread here already:

SELECT *
FROM Country
WHERE Name LIKE CONCAT("%",:param,"%")

Could you build up your statement with a function like this?

Regards, Felix [Scandio]