8 Replies Latest reply on Feb 18, 2016 10:06 AM by WolfShade

    QoQ with WHERE clause using a list is breaking

    WolfShade Level 4

      Hello, all,

       

      I've got a project where I need to use a list as part of the WHERE clause for a QoQ, and it's breaking.  I'm not sure what I'm doing incorrectly.

       

      For some pseudo-code, pretend I have a full query from a database called "people".  I get a list of values for the QoQ with the first query; the second query is my QoQ:

       

      <cfquery name="getDIR" datasource="#request.THISDSN#">
          SELECT thisName dir FROM orgs WHERE thisID in (<cfqueryparam value="#form.org#" cfsqltype="cf_sql_varchar" list="yes" />)
      </cfquery>
      
      <cfquery name="people" database="people">
          SELECT colA, colB, colC, colD
          FROM people
          WHERE 1=1
              AND (
                  div IN (<cfqueryparam value="#valueList(getDIR.dir)#" cfsqltype="varchar" list="yes" />)
                  OR dir IN (<cfqueryparam value="#valueList(getDIR.dir)#" cfsqltype="varchar" list="yes" />)
                  )
      </cfquery>
      
      
      

       

      The error message I'm getting is "QoQ syntax error: Encountered "div IN \'thisValue\'".  Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition."

       

      Can QoQ _NOT_ support lists for WHERE clauses?

       

      V/r,

       

      ^_^