• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

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"><!---   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>
                    <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!
0
henderxe
Asked:
henderxe
  • 2
1 Solution
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
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.
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
also if you can show me what getCodes is coming as like dump the getcodes value and show me what value is coming, it might be some kind of single or double quote issue,
0
 
_agx_Commented:
(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....
0
 
henderxeAuthor Commented:
Tnank you for the solution, as well as all the other useful comments.

henderxe
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now