Query of Queries variable scoping

Question

Is it possible to scope variables when using query of queries? This works

return queryExecute("
        SELECT Title, Slug, Menu, MenuOrder
        FROM qryResult
        WHERE Menu = 'Y'
        ORDER BY MenuOrder
        ",
        [],
        {dbtype = "query"}
        );

This does not work. It throws an error

return queryExecute("
        SELECT Title, Slug, Menu, MenuOrder
        FROM local.qryResult
        WHERE Menu = 'Y'
        ORDER BY MenuOrder
        ",
        [],
        {dbtype = "query"}
        );

Message
Query Of Queries syntax error.
Encountered "local. StackTrace java.sql.SQLException:
Query Of Queries syntax error.
Encountered "local. at coldfusion.sql.imq.jdbcStatement.parseSQL(jdbcStatement.java:590) at coldfusion.sql.imq.jdbcStatement.fetchResult(jdbcStatement.java:547) at ORDER BY MenuOrder ", [], {dbtype = "query"} );

What does the 2nd sample do that constitutes, "does not"?

Dan Bracuk
– Dan Bracuk

2015-11-04 04:07:15 +00:00
Commented Nov 4, 2015 at 4:07 — Dan Bracuk
– Dan Bracuk, Commented Nov 4, 2015 at 4:07
Try wrapping local within square brackets.

Abhishekh Gupta
– Abhishekh Gupta

2015-11-04 04:38:53 +00:00
Commented Nov 4, 2015 at 4:38 — Abhishekh Gupta
– Abhishekh Gupta, Commented Nov 4, 2015 at 4:38
[local].qryResult works!

James A Mohler
– James A Mohler

2015-11-04 04:54:49 +00:00
Commented Nov 4, 2015 at 4:54 — James A Mohler
– James A Mohler, Commented Nov 4, 2015 at 4:54

Abhishekh Gupta · Accepted Answer · 2015-11-04 06:06:05Z

7

LOCAL is a reserved word in ColdFusion Query of Query and SQL.

So, wrap LOCAL with square brackets[] like this:

return queryExecute("
        SELECT Title, Slug, Menu, MenuOrder
        FROM [local].qryResult
        WHERE Menu = 'Y'
        ORDER BY MenuOrder
        ",
        [],
        {dbtype = "query"}
        );

OR

Use some other name for function local scope i.e.,

var newLocal = structNew();
newLocal.qryResult = queryNew("");
return queryExecute("
        SELECT Title, Slug, Menu, MenuOrder
        FROM newLocal.qryResult
        WHERE Menu = 'Y'
        ORDER BY MenuOrder
        ",
        [],
        {dbtype = "query"}
        );

answered Nov 4, 2015 at 6:06

Abhishekh Gupta

6,2464 gold badges20 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query of Queries variable scoping

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related