You are here:
Adobe MAX - The premier Adobe event! Adobe MAX - The premier Adobe event!

LOCAL scoped variables and Query of Query conflict syntax error

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