SQL IN statement

[fshin]

Hi,

I'm trying to run a query that looks something like this

<cfquery name="test" datasource = "ds1">
SELECT *
FROM tblTest
WHERE '#variables.test#' IN (txCommaDelimitedList)
</cfquery>

where variables.test is an integer and txCommaDelimitedList is a database field like '100','101','102'. This doesnt work though. Any ideas?

[Dan_Bracuk]

Normalize your database and you won't have problems like this.

[fshin]

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.

[Kronin555]

I'll echo what Dan said:

> 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.

[The_ScareCrow]

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

[dallaswebb]

You'd need to loop over variables.test and create dynamic statement.

<cfset tempSQLStatement ="WHERE #listFirst(variables.test)# IN (txCommaDelimitedList)" />

<cfloop list="#listRest(variables.test)#" index="i">
<!--- you could also use OR --->
<cfset tempSQLStatement = "AND #i# IN (txCommaDelimitedList)" />

</cfloop>

<cfquery name="test" datasource = "ds1">
SELECT *
FROM tblTest
#tempSQLStatement#
</cfquery>

[joeDangelo]

Whats the error you get?

[joeDangelo]

Why is variables.test in quotes?

[fshin]

bc txCommaDelimitedList is a varchar field since it has the commas in sql

[paross1]

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

[insuractive]

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 <cfqueryparam> you can use the list="Yes" parameter to handle comma delimited lists (even integers).

WHERE #Variables["test"]# IN (<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#txCommaDelimitedList#" list="Yes">)

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