asked on

How to Add an AND clause using the ListQUalify Function

Hello:

I'm trying to ad an "AND" clause to the code below. In other words, the logic needs to be as follows:

SELECT Field1, Field2
FROM table1
WHERE field1 IN (#listQualify(newList, "'")#)
AND field2 IN ((#listQualify(newList, "'")#) - (Can't get this one to work).

=========================START CODE ============================

<cffunction name="getEngagementCommittee" access="public" returntype="any">
            <cfargument name="getCodes" type="string" required="yes">
      <cfset resultSet="">
            <cfset newList = "">
            <cfloop list="#getCodes#" index="j" >
            <cfset newList = listAppend(trim(j),newList,",")>
</cfloop>
<cfquery name="resultSet" datasource="#db.dbName#" username="#db.dbUserName#" password="#db.dbPassword#">
select committee_code code, committee_code||' - '||short_desc description ,short_desc
from advance.committee_header
where committee_group_code in (#listQualify(newList, "'")#)

NOTE: Below is where I need the AND clause. It does NOT work. I think SYNTAX issue.

AND parent_committee_group IN (#listQualify(newList, "'")#)

order by short_desc

</cfquery>
            <cfreturn resultSet>
      </cffunction>

=========================END CODE ============================

Thank you!

Coast Line

Just split it like this:

<cffunction name="getEngagementCommittee" access="public" returntype="any"><!---   Populate city based on the state we have selected --->  
            <cfargument name="getCodes" type="string" required="yes">
              <cfset resultSet="">  
                        <cfset newList = "">
                        <cfloop list="#getCodes#"    index="j" >
                                <cfset newList = listAppend(trim(j),newList,",")>                                
                    </cfloop>
<cfset newQualifiedLst = listQualify(newList, "'")>
                    <cfquery name="resultSet"  datasource="#db.dbName#"  username="#db.dbUserName#" password="#db.dbPassword#">
 select committee_code code, committee_code||' - '||short_desc description ,short_desc
 from advance.committee_header
where committee_group_code in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#newQualifiedLst#" list="yes">)  

     AND parent_committee_group IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="#newQualifiedLst#" list="yes">)

order by short_desc

                </cfquery>    
            <cfreturn resultSet>
      </cffunction>

Open in new window

Ok, getCodes is coming as comma separated list, right

you are just adding the quotes around the list you having, correct me if i am wrong

if i am correct you can merge the listappend and listqualify in the single call inside the loop rather than doing it again after the loop.

ASKER CERTIFIED SOLUTION

Coast Line

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

(no points...)

Yeah, don't use #listQualify(newList, "'")# in queries. Aside from possibly causing syntax errors if the values contain quotes, it may also expose your database to sql injection.

As mentioned above, use <cfqueryparam> with the "list" attribute. That attribute tells the db to treat the input as a list of individual values, and eliminates the need for quoting. It should solve your issue.

Side note - don't forget to var scope ALL of the function local variables: "j", "newList", "resultSet", etc....

henderxe

ASKER

Tnank you for the solution, as well as all the other useful comments.

henderxe