I'm trying to run a query that looks something like this
%26lt;cfquery name=''test'' datasource = ''ds1''%26gt;
SELECT *
FROM tblTest
WHERE '#variables.test#' IN (txCommaDelimitedList)
%26lt;/cfquery%26gt;
where variables.test is an integer and txCommaDelimitedList
is a database field like '100','101','102'. This doesnt work
though. Any ideas?SQL IN statement
Normalize your database and you won't have problems like
this.SQL IN statement
You'd need to loop over variables.test and create dynamic
statement.
%26lt;cfset tempSQLStatement =''WHERE
#listFirst(variables.test)# IN (txCommaDelimitedList)'' /%26gt;
%26lt;cfloop list=''#listRest(variables.test)#'' index=''i''%26gt;
%26lt;!--- you could also use OR ---%26gt;
%26lt;cfset tempSQLStatement = ''AND #i# IN
(txCommaDelimitedList)'' /%26gt;
%26lt;/cfloop%26gt;
%26lt;cfquery name=''test'' datasource = ''ds1''%26gt;
SELECT *
FROM tblTest
#tempSQLStatement#
%26lt;/cfquery%26gt;
Whats the error you get?
Actually, my example was bad. It should have read:
SELECT *
FROM view_test
WHERE '#variables.test#' IN (txCommaDelimitedList)
It's a view where txCommaDelimitedList is a field which
contains a comma delimited list of integers based on the results of
a query of an xref table.
No errors. Just no results.
Why is variables.test in quotes?
bc txCommaDelimitedList is a varchar field since it has the
commas in sql
so which one is your field, and which one is your set of
values? You can NOT do this:
SELECT something
FROM table
WHERE 'value' IN (COLUMN_name1, COLUMN_Name2, etc.)
This is invalid SQL!
Should be more like
WHERE COLUMN_Name IN('value','value','etc.')
Phil
I can see a few obvious problems with the SQL:
1) You should not use quotes surrounding #variables.test#.
The resulting SQL should be:
WHERE myCol IN (myList)
and not
WHERE 'myCol' IN (myList)
2) You don't have txCommaDelimitedList surrounded by pound
signs, so CF is treating it as the literal text:
''txCommaDelimitedList''. Look into using %26lt;cfqueryparam%26gt; you
can use the list=''Yes'' parameter to handle comma delimited lists
(even integers).
WHERE #Variables[''test'']# IN (%26lt;cfqueryparam
cfsqltype=''CF_SQL_INTEGER'' value=''#txCommaDelimitedList#''
list=''Yes''%26gt;)
3) Using a CF variable like that in your query statement is
incredibly insecure. You're pretty much setting yourself up for a
SQL injection attack. You might look into using a switch/case
statement, or at least performing an initial query to make sure
that Variables.test is a valid column name and not something like:
1=1;
DROP TABLE
SELECT * from SomeTable WHERE 1
I'll echo what Dan said:
%26gt; Normalize your database and you won't have problems like
this.
What you're trying to do is not possible:
WHERE 'cfvariable' IN (databasecolumn)
where the databasecolumn contains values like this:
'''101','200','204','204'''
You're trying to do a reverse IN. SQL IN statements are meant
to check a database column against a comma-delimited list of
values, not the other way around (a value against a column in your
database that contains a comma-delimited list of values).
You need to fix your data model.
My 2c,
Because fshin has said
quote:
It's a view where txCommaDelimitedList is a field which
contains a comma delimited list of integers based on the results of
a query of an xref table.
I would then say the db may be normalized.
Questions:
How does the cf page access this ''view'' ?
Can you change the ''view'' or pass in a parameter ?
The query you are trying to perform can't be done.
Maybe easier to use list functions to see if the ''test'' value
is in the ''view'' list, then use the result in a query.
Ken
