Help with creating a list

[mega_L]

I query id values from one of my table.

I need to come up with a list that looks like:

id='123' OR id='234' OR id='002' OR id='345' OR id='435'

I came up with 123,234,002,345,435 and '123' , '234' , '002' , '435' and also '123' OR '234' OR '002' OR '435'

but very frustrated with putting id= on each list element to come up with id='123' OR id='234' OR id='002' OR id='345' OR id='435'

Can anyone think of a technique to create such a list dynamically from a query result?

Thanks!

[JR__Bob__Dobbs]

Use CFQUERYPARAM with the list attribute in your query.

<cfset myList="A100,B200,C300">

<cfquery name="myQuery">

     SELECT *

     FROM myTable

     WHERE idKey IN (  <cfqueryparam cfsqltype="cf_sql_char" list="yes" value="#myList#">)

</cfquery>

[Dan_Bracuk]

Depending on where the data is coming from, you might be able to achieve the same thing by having a single query that selects from more than one table.

[mega_L]

I think I'm not too clear with my plea for help.

I'm not going to use the list in my query. I need to create this list so I can send it through web services to get something back.

I was told that the web services does not take in id IN ('123','234','002','354') but it takes in id='123' OR id='324' OR id='435' OR etc

[JR__Bob__Dobbs]

You could create a user defined function that converts a comma delimited list of values into one formatted with id's and OR's.

Here is a quick sample which has NOT been tested.

<cffunction name="customFormatOrList" returntype="string" output="no" hint="Converts comma delimited list to OR query string">
    <cfargument name="inputList" type="string" required="yes" hint="Comma delimited list" />

    <cfset var local=StructNew() />

    <cfset local.resultString="" />

    <cfloop list="#arguments.inputList#" index="local.idx">

        <!--- add 'OR' if this is not the first item --->
        <cfif Len(local.resultString) gt 0>
            <cfset local.resultString="#local.resultString# OR " />
        </cfif>

        <cfset local.resultString="#local.resultString#id='#local.idx#'" />

    </cfloop>

    <cfreturn local.resultString />

</cffunction>

[mega_L]

Thank you!! I tested and it works. I just needed to tweak my list a bit.

[Dan_Bracuk]

t some point you need to deal with the extra "or".  I'd do this:

MyNewList = "1=2";

looping through value list from query.

MyNewList = MyNewList & " or id = '#value#'";

Then send MyNewList to the web service.