Status: Open
Status: Answered
Status: Closed
Status: Duplicate

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

0
Posted Apr 03 by Pierre Ballon.

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.

 

 

2 Answers

0
BEST ANSWER: As chosen by the author.

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 …)


0
BEST ANSWER: As chosen by the author.

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.


 You have subscribed and will receive email notifications of updates to this topic. To unsubscribe, uncheck the checkbox.

Statistics

Related categories

Related tags

Your answer

To leave an answer, please sign in.