Skip to main content
S
spacehog
Inspiring
April 29, 2009
Answered

Query of queries question

  • April 29, 2009
  • 2 replies
  • 620 views

Can you query a query created using queryNew()?

When I try the following code I get an error:

"Query Of Queries syntax error.
Encountered "qData . user. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, "

Code:

<cfset qData = queryNew("tool,user,community,fm01,fm02,fm03,fm04,fm05,fm06,fm07,fm08,fm09,fm10,fm11,fm12,fy")>
<cfset temp = #queryAddRow(qData)#>
<cfset temp = #querysetCell(qData,"tool","empty")#>
<cfset temp = #querysetCell(qData,"user","Bob")#>

<cfquery  dbtype="query" name="chkQdata">
       SELECT *
       FROM qData
       WHERE qData.user = 'Bob'
</cfquery>

    This topic has been closed for replies.
    Correct answer craigkaminsky

    I just tried the code you supplied, adding in two cfdump tags (the first to dump the qData query and the second to dump the chkQdata query). Everything worked fine exactly as you had it on Railo 3.1. It did not work (threw the error you got) on CF 8.0.1.

    If I removed the WHERE clause from the chkQdata query, it runs (no errors) in CF 8.0.1.

    To get it to run as you want in CF8, change the user column. This appears to be a reserved keyword for CF8. While I did not verify that exactly, if I change your original query to be tool = 'emtpy', it runs. Also, renaming the user column to usr results in the script running properly.

    Here's the code I got to work in Railo 3.1 and CF8:

    <cfset qData = queryNew("tool,usr,community,fm01,fm02,fm03,fm04,fm05,fm06,fm07,fm08,fm09,fm10,fm11,fm12,fy","varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar")>

    <cfset temp = #queryAddRow(qData)#>

    <cfset temp = #querysetCell(qData,"tool","empty")#>

    <cfset temp = #querysetCell(qData,"usr","Bob")#>

    <cfdump var="#qData#" />

    <cfquery dbtype="query" name="chkQdata">

    SELECT *

    FROM qData

    WHERE usr = 'Bob'

    </cfquery>

    <cfdump var="#chkQdata#" />

    2 replies

    M
    mweezer
    Participant
    April 29, 2009

    Try:

    <cfquery  dbtype="query" name="chkQdata">
           SELECT *
           FROM qData
           WHERE qData.user LIKE 'Bob'
    </cfquery>

    Text fields (at least in my experience with MS SQL) can't use a '='

    S
    spacehogAuthor
    Inspiring
    April 30, 2009

    'like' had the same error issue.  It was that user is a reserved word.  It was odd because I never use the word user, usually I do 'uName' or 'userN'.

    C
    craigkaminskyCorrect answer
    Inspiring
    April 29, 2009

    I just tried the code you supplied, adding in two cfdump tags (the first to dump the qData query and the second to dump the chkQdata query). Everything worked fine exactly as you had it on Railo 3.1. It did not work (threw the error you got) on CF 8.0.1.

    If I removed the WHERE clause from the chkQdata query, it runs (no errors) in CF 8.0.1.

    To get it to run as you want in CF8, change the user column. This appears to be a reserved keyword for CF8. While I did not verify that exactly, if I change your original query to be tool = 'emtpy', it runs. Also, renaming the user column to usr results in the script running properly.

    Here's the code I got to work in Railo 3.1 and CF8:

    <cfset qData = queryNew("tool,usr,community,fm01,fm02,fm03,fm04,fm05,fm06,fm07,fm08,fm09,fm10,fm11,fm12,fy","varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar")>

    <cfset temp = #queryAddRow(qData)#>

    <cfset temp = #querysetCell(qData,"tool","empty")#>

    <cfset temp = #querysetCell(qData,"usr","Bob")#>

    <cfdump var="#qData#" />

    <cfquery dbtype="query" name="chkQdata">

    SELECT *

    FROM qData

    WHERE usr = 'Bob'

    </cfquery>

    <cfdump var="#chkQdata#" />

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices