CODEWITHSUNDEEP
sqllistcoldfusioncfquery
IrfanClemson
IrfanClemson

Reputation: 1779

ColdFusion: SQL Select IN from a Query

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!

Upvotes: 3

Views: 2555

Answers (2)

IrfanClemson
IrfanClemson

Reputation: 1779

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" />
    )

Upvotes: 3

AJ Dyka
AJ Dyka

Reputation: 46

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

Upvotes: 3

Related Questions