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 name="people" database="people">
SELECT colA, colB, colC, colD
div IN (<cfqueryparam value="#valueList(getDIR.dir)#" cfsqltype="varchar" list="yes" />)
OR dir IN (<cfqueryparam value="#valueList(getDIR.dir)#" cfsqltype="varchar" list="yes" />)
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?
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*
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.
I did, just to clean the thread. Probably should have waited. Do you need me to unmark it?
-Carl V. - Moderator
Don't worry. I don't think the forum gives points to self-answered questions.
It wasn't the points that I was worried about, it was the perception. Don't want to be "that guy".
No worries. I think people would rather see the solution quickly than wade through all the thread replies to find the answer, even if the OP is the one who answers the question.
Also, if anyone posts a question that turns out to be a PEBKAC issue, you can delete the question if you don't think anyone else will encounter the same issue the same way.
Oh, I definitely felt quite eye dee ten tee, after I discovered the truth. But just in case someone else has the same issue.. (shrug)