When working for a client recently, I stumbled across a slightly annoying problem when using a LOCAL scoped query as the source of my ColdFusion Query of Query. I received the error:
Query Of Queries syntax error.
Encountered "LOCAL.
If I changed the scope of the variable to something other than LOCAL, the Query of Query would work no problem. Why is this?
A little Google and I found the problem (thanks to Ben Nadel's post on - LOCAL Variables Scope Conflicts With ColdFusion Query of Queries).
The word LOCAL is a reserved word in SQL.and ColdFusion Query of Query SQL. A simple solution to the problem, use [ ] notation.
<cfquery name="local.demoQuery" dbtype="query">
SELECT *
FROM [LOCAL].DemoQuery
WHERE id = 1
</cfquery>
Thanks again to Ben for the assistance and guidance