Highlighted

QoQ with WHERE clause using a list is breaking

LEGEND ,
Feb 17, 2016

Copy link to clipboard

Copied

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,

^_^

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,

^_^

Views

269

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

QoQ with WHERE clause using a list is breaking

LEGEND ,
Feb 17, 2016

Copy link to clipboard

Copied

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,

^_^

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,

^_^

Views

270

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Feb 17, 2016 0
LEGEND ,
Feb 17, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 17, 2016 0
LEGEND ,
Feb 17, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 17, 2016 0
Most Valuable Participant ,
Feb 17, 2016

Copy link to clipboard

Copied

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

-Carl V. - Moderator

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 17, 2016 0
LEGEND ,
Feb 18, 2016

Copy link to clipboard

Copied

Hi, Carl Von Stetten‌,

Thank you, no.  It is, technically, the correct answer.  I just didn't want anyone to think that I was trying to give unwarranted credit to myself. 

V/r,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 18, 2016 0
Most Valuable Participant ,
Feb 18, 2016

Copy link to clipboard

Copied

Don't worry.  I don't think the forum gives points to self-answered questions.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 18, 2016 0
LEGEND ,
Feb 18, 2016

Copy link to clipboard

Copied

It wasn't the points that I was worried about, it was the perception.  Don't want to be "that guy". 

V/r,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 18, 2016 0
Most Valuable Participant ,
Feb 18, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 18, 2016 0
LEGEND ,
Feb 18, 2016

Copy link to clipboard

Copied

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)

V/r,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 18, 2016 0