Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread Problem with "where col in ()"
Sun, Oct 9 2016 1:16 PMPermanent Link

Jiri Dvorsky

Avatar

Hi All,
I have this problem, in my dataset "Row Source" section I need do:
SELECT * from TAB1 where (col1 in (val1, val2, val3 .....) )  
col1 is indexed integer value and I need send values in parametr.
So, I think this declaration should work:
SELECT * from TAB1 where (col1 in ({list_values}) )

Bat when I test it, then result from select contains row only for val1,
content after the first comma is ignored.
When I test for example this:
SELECT * from TAB1 where (col1 in ({list_values=3377,3376}) )
in "Preview" section I can see only row where col1=3377.
SQL server is Firebird and when this select runs in ISQL, result is OK.
Can anybody confirm if clausule "where column in ()" with param  works well on EWB server ?

Thanks,
Jiří Dvorský
Tue, Oct 11 2016 1:14 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Jiri,

<< I have this problem, in my dataset "Row Source" section I need do: >>

Unfortunately, you have to specify the parameter names/values separately, and cannot just use a comma-delimited list of values as the value for one parameter.

In other words, you have to use something like this:

SELECT * FROM MyTable
WHERE MyColumn IN ({Param1='Test'}, {Param2='Test'})

This has to do with how EWB parses the SQL and parameterizes it for SQL Server, MySQL, etc.  It uses actual SQL parameters for the various parameters, and most database engines/servers do not support multiple values within a single parameter.

Tim Young
Elevate Software
www.elevatesoft.com
Tue, Oct 11 2016 2:30 PMPermanent Link

Jiri Dvorsky

Avatar

Thank you for your answer, I understand. But in real-world applications, the clause "WHERE IN ()" is used to indexed selection 1-n values (the number of parameters is not known). I'm going to write user modules.   Frown


****************************************************************************************************************************

Tim Young [Elevate Software] wrote:

Jiri,

<< I have this problem, in my dataset "Row Source" section I need do: >>

Unfortunately, you have to specify the parameter names/values separately, and cannot just use a comma-delimited list of values as the value for one parameter.

In other words, you have to use something like this:

SELECT * FROM MyTable
WHERE MyColumn IN ({Param1='Test'}, {Param2='Test'})

This has to do with how EWB parses the SQL and parameterizes it for SQL Server, MySQL, etc.  It uses actual SQL parameters for the various parameters, and most database engines/servers do not support multiple values within a single parameter.

Tim Young
Elevate Software
www.elevatesoft.com
Mon, Oct 17 2016 1:17 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Jiri,

<< Thank you for your answer, I understand. But in real-world applications, the clause "WHERE IN ()" is used to indexed selection 1-n values (the number of parameters is not known). >>

Yes, and this is a known issue with SQL parameters in just about every SQL database that supports parameters, including SQL Server.  It's not specific to EWB's dataset access.  The problem is that SQL with parameters is supposed to be compiled and the parameters bound at compilation time.  If the number of parameters (and possibly their types) is dynamic, then you cannot reconcile this fact with the ahead-of-time compilation of parameterized SQL.

Tim Young
Elevate Software
www.elevatesoft.com
Image