Query of queries question

[spacehog]

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>

[craigkaminsky · Accepted solution]

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#" />

[mweezer]

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 '='

[spacehog]

'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'.