Skip to main content
WolfShade
Legend
February 17, 2016
Answered

QoQ with WHERE clause using a list is breaking

  • February 17, 2016
  • 1 reply
  • 649 views

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,

^_^

This topic has been closed for replies.
Correct answer WolfShade

SMH - Nevermind.. I just found it.

I forgot to give the lists in the second query parenthesis.


(The pseudo-code was manually typed, since my dev system is isolated from the internet, and I added them in my pseudo-code when they were not in my original code.)

*headdesk* *headdesk* *headdesk* *headdesk*

V/r,

^_^

1 reply

WolfShade
WolfShadeAuthorCorrect answer
Legend
February 17, 2016

SMH - Nevermind.. I just found it.

I forgot to give the lists in the second query parenthesis.


(The pseudo-code was manually typed, since my dev system is isolated from the internet, and I added them in my pseudo-code when they were not in my original code.)

*headdesk* *headdesk* *headdesk* *headdesk*

V/r,

^_^

WolfShade
WolfShadeAuthor
Legend
February 17, 2016

Just for the record:  I DID NOT MARK MY SECOND POST AS THE CORRECT ANSWER.  Someone else did that, and by the time I noticed, it was too late to unmark it as correct.

V/r,

^_^

Carl Von Stetten
Legend
February 17, 2016

I did, just to clean the thread.  Probably should have waited. Do you need me to unmark it?

-Carl V. - Moderator