Version 9.3 / Query Business Object using IN (@Parameter)

Ballon_Pierre

Hi,

 

I'm populationg a grid with the following bo/Query:

 

SELECT Company.CAMPAIGN_YN, Company.RAISON_SOCIALE,

FROM Company, People

WHERE

Company.SIRET=People.SIRET 

and

left(Company.CP,2) IN (@memDpts).

 

The local variable memDpts is a memo populated using a List field on the form and is bound with @memDpts.

This work fine only if @memDpts is a single value ("65" for example).

What syntax, type ,...  should I use to make it work with a list (really using the IN), I tried many things (add quotes , commas, double quotes,...), but I can't fix it.

Thanks for help.

 

 

Brian Mellert

I never got IN clauses to work well when the are in put parameters.  (They work fine for constants or in-line code.)

 

The only way I've sort of gotten around this is to wrap the whole SQL inside an EXEC where so the string can use + to build up the entire query as desired.  I've not gotten that to work very well very often, and I seem to lose the ability to use it on grids as the fields are no longer "available".  (Or I'm doing something wrong, which is quite possible.)

 

I believe Jerome has suggested elsewhere (here and/or his web site) to insert the keys / folders / values into a temporary table and join or used that table in your IN clause for your list.  (Actually this may resolve similar issues I've not looped back to yet ...)

Nils Wyldbore

Pierre,

 

Take a look at the dbo.pudf_stringlist_to_table function that ships with MBPM:

 

... AND LEFT(Company.CP, 2) IN (SELECT Item FROM dbo.pudf_stringlist_to_table(@memDpts, ','))

 

Assuming that @memDpts is a comma separated list of companies.

 

Hope this helps,

 

Nils.