Using variable in SQL IN predicate

[robnollie]

I am using a routine to build a variable called "regions" which can be any
combination of 1,2,3 depending on the user's selection of sales regions. So
regions could be just 1 or 1,3 or 1,2,3.

I want to use a variable built with concatenation called #regions# in the
following segment

<cfquery dbtype="query" name="detail5">
select *
from detail4
where region IN (#regions#);
</cfquery>

Using CFOUTPUT the string appears correct i.e. '1','2','3' or other variation
but always correctly formed. HOwever using the variable throws an error.


Query Of Queries syntax error.
Encountered "region IN ( \'\' 1" at line 0, column 0. Incorrect conditional
expression, Expected one of [like|null|between|in|comparison] condition,

The error occurred in D:\inetpub\chilli\chillidm\onelan\search1.cfm: line 71

69 : select *
70 : from detail4
71 : where region IN (#regions#);
72 : </cfquery>
73 : <cfoutput>#regions#</cfoutput>




--------------------------------------------------------------------------------

SQL select * from detail4 where region IN (''1'',''2'',''3'');

Note the " are in fact two apostrophes.

ANy clues?

Cheers

Rob

[robnollie]

I am using a routine to build a variable called "regions" which can be any
combination of 1,2,3 depending on the user's selection of sales regions. So
regions could be just 1 or 1,3 or 1,2,3.

I want to use a variable built with concatenation called #regions# in the
following segment

<cfquery dbtype="query" name="detail5">
select *
from detail4
where region IN (#regions#);
</cfquery>

Using CFOUTPUT the string appears correct i.e. '1','2','3' or other variation
but always correctly formed. HOwever using the variable throws an error.


Query Of Queries syntax error.
Encountered "region IN ( \'\' 1" at line 0, column 0. Incorrect conditional
expression, Expected one of [like|null|between|in|comparison] condition,

The error occurred in D:\inetpub\chilli\chillidm\onelan\search1.cfm: line 71

69 : select *
70 : from detail4
71 : where region IN (#regions#);
72 : </cfquery>
73 : <cfoutput>#regions#</cfoutput>




--------------------------------------------------------------------------------

SQL select * from detail4 where region IN (''1'',''2'',''3'');

Note the " are in fact two apostrophes.

ANy clues?

Cheers

Rob

[Jochem van Dieten - TMM]

robnollie wrote:

>
> I want to use a variable built with concatenation called #regions# in the
> following segment
>
> <cfquery dbtype="query" name="detail5">
> select *
> from detail4
> where region IN (#regions#);
> </cfquery>

Use cfqueryparam:

select *
from detail4
where region IN (<cfqueryparam cfsqltype="cf_sql_integer"
value="#regions#" list="yes">)

Jochem

--
Jochem van Dieten
Team Macromedia Volunteer for ColdFusion, beer and fun.

[TA-Selene]

In the query, you want to have your variable inside the PreserveSingleQuotes
function, which will escape the quotes in the string. If region is a number
column, though, you could just skip the quotes all together.