ColdFusion: SQL Select IN from a Query

Question

I have a SQL Server query which returns two values for one MyBusinessUnit column returns two values, say:

1111

2222

in a query object called MyQuery1

Both of these values also exist in a DB2 database's MyCorpUnit column.

What I want is to select all the matching records from the DB2 table--and, no, cross database queries are NOT working.

So, here's my Query2 for DB2 database:

 <cfquery name="Query2" datasource="#application.DSN#"> 
  SELECT MyCorpUnit WHERE MyCorpUnit IN
  (    
     <cfqueryparam value="   #Query1.MyBusinessUnit #" CFSQLType="cf_sql_varchar" />
  )
 </cfquery>

But Query2 only returning matching record for only one value (1111).

So some other approach is needed. I have tried to create a string but that didn't work either.

Any idea?

Thanks!

*only returning matching record for only one value* Take a look at the debug output for the query (or dump the cfquery "result" attribute) to see what values are passed into query2. Hint: It is probably *not* "1111,2222" — Leigh, Feb 15 '17 at 03:17

score 3 · Accepted Answer · answered Feb 15 '17 at 02:02

3

cfqueryparam has a list attribute which might help:

<cfqueryparam value = "parameter value" 
        CFSQLType = "parameter type" 
        list = "yes|no" 
        maxLength = "maximum parameter length" 
        null = "yes|no" 
        scale = "number of decimal places" 
        separator = "separator character"> 
    AND/OR ...additional criteria of the WHERE clause...>

I've used it before but not sure if it was in a QoQ or not. :D

ref: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html

answered Feb 15 '17 at 02:02

AJ Dyka

46
2

Thanks! Sounds interesting. But, as you can see, my values have to have 3 leading spaces before the values. How will that work? – IrfanClemson Feb 15 '17 at 02:08
1

Add the space in your initial query or trim the column you're checking against – Matt Busche Feb 15 '17 at 02:15
Initial query: Select DISTINCT SUBSTRING(MyBusinessUnit , 1, 9) AS WStreamBusinessUnit – IrfanClemson Feb 15 '17 at 02:18
I have set a List variable which outputs to 1111,2222. Then using this but it only returns 1111 – IrfanClemson Feb 15 '17 at 02:27
@Meengla - Do the values really have leading spaces? Ugh.. using a function on a column usually kills any chance the db will utilize indexes. – Leigh Feb 15 '17 at 04:54

score 3 · Answer 2 · edited Feb 15 '17 at 04:53

I am going to accept @AJ Dyka answer [Thank you!] but I have more to add to make it complete. Indeed, per his advice, I ended up using the 'LIST' attribute. A good discussion on it can be found here.

But, as you can see in the comments, I was still getting only "1111" despite using a List. And that's because of the leading spaces in my data. I ended up using a TRIM function. Here is a code snippet.

Converted the output from Query1 to a List :

<cfset ListUniqueWStreamBusinessUnit = ValueList(Query1.MyBusinessUnit )>

Then the magical code snippet!

    ...
    WHERE trim(GMMCU) IN 
    (   
       <cfqueryparam value="#ListUniqueWStreamBusinessUnit#" 
           CFSQLType="cf_sql_varchar"   
           list="yes" />
    )

ColdFusion: SQL Select IN from a Query

2 Answers2

Linked