ColdFusion: SQL从查询中选择

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

我有一个SQL Server查询，它返回一个MyBusinessUnit列的两个值返回两个值，比如:

1111

1111年

2222

2222年

in a query object called MyQuery1

在一个名为MyQuery1的查询对象中

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

这两个值也存在于DB2数据库的MyCorpUnit列中。

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

我想要的是从DB2表中选择所有匹配的记录——不，跨数据库查询不起作用。

So, here's my Query2 for DB2 database:

下面是DB2数据库的Query2:

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

但是Query2只返回一个值的匹配记录(1111)。

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

所以我们需要其他的方法。我试图创建一个字符串，但它也不起作用。

Any idea?

任何想法?

Thanks!

谢谢!

2 个解决方案

#1

cfqueryparam has a list attribute which might help:

cfqueryparam有一个列表属性，可以帮助:

<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

我以前用过，但不确定是否在QoQ中。:D

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

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

#2

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.

我将接受@AJ Dyka的回复[谢谢![英语背诵文选但我还有更多的东西要补充才能使它完整。事实上，根据他的建议，我最终使用了“LIST”属性。在这里可以找到关于它的很好的讨论。

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.

但是，正如您在评论中看到的，尽管使用了列表，我仍然只能得到“1111”。这是因为数据中的前导空间。最后我用了一个修整函数。这是一个代码片段。

Converted the output from Query1 to a List :

将Query1的输出转换为列表:

<cfset ListUniqueWStreamBusinessUnit = ValueList(Query1.MyBusinessUnit )>

Then the magical code snippet!

然后是神奇的代码片段!

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

#1