Status/Resolution/Reason: To Fix//
Reporter/Name(from Bugbase): Stephen Baker / Stephen Baker (Stephen Baker)
Created: 03/20/2015
Components: Language
Versions: 10.0
Failure Type:
Found In Build/Fixed In Build: Final /
Priority/Frequency: Normal / All users will encounter
Locale/System: English / Win 2008 Server R2 64 bit
Vote Count: 0
Problem Description:
Contrary to the documentation it doesn't appear that a SELECT statement is allowed in the IN clause of a query of queries cfquery: http://help.adobe.com/en_US/ColdFusion/10.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html#WSc3ff6d0ea77859461172e0811cbec0e4fd-7fda
Steps to Reproduce:
<CFQUERY name="variables.fields" dbtype="query">
SELECT fieldname,
ID
FROM udfs
WHERE [Position] IN ( SELECT [Position] FROM UDFValues )
</CFQUERY>
Actual Result:
<br><b>Query Of Queries syntax error.</b><br> Encountered "[Position] IN ( SELECT. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,
Expected Result:
A query containing all rows of udfs where Position matches a Position in the query UDFValues
Any Workarounds:
In this case I could use a join and a group by to get the same results. Another possible workaround would be to ValueList position in UDFValues ahead of time and include that list in the IN.
----------------------------- Additional Watson Details -----------------------------
Watson Bug ID: 3956874
Reason: BugVerified
External Customer Info:
External Company:
External Customer Name: Stephen
External Customer Email:
External Test Config: My Hardware and Environment details:Attachments:
Comments: