Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2014 (Bloomington, MN) with:

Encountered "(. Incorrect Select Statement, Expecting a 'FROM', But Encountered '(' Instead, A Select Statement Should Have a 'FROM' Construct.

By Ben Nadel on
Tags: ColdFusion, SQL

I was testing some ColdFusion Query of Queries functionality when I got this error:

Query Of Queries syntax error.
Encountered "(. Incorrect Select Statement, Expecting a 'FROM', But Encountered '(' Instead, A Select Statement Should Have a 'FROM' Construct.

I know what was causing the error because I was testing to see if something worked, but it's not the most obvious error. I guess it's a syntax error (from a technical standpoint), but in my case, where I was testing to see if ISNULL() worked, I'm really using a function that is not available.

That being said, if you get the error above, it means you are using something in the SELECT statement that is not allowed. If you are coming over from SQL, make sure you are not trying to use any SQL functions that are not available in the ColdFusion Query of Queries. As a foot note, here are some common SQL functions that I use that are not available in the ColdFusion Query of Queries:

ISNULL()
LEN()
DATEADD()



Reader Comments

Hrm...

In my case, I have an XML file which I'm converting into a query object. The resulting query has a column named date. I can select * just fine, but when I specify the date column it blows up. Leave that column out and it works just fine. I've tried bracketing the column name, quoting it bracketing AND quoting to no avail.

Anyone have any ideas?

Reply to this Comment

Okay...

In this case, the solution was ot both bracket AND alias the questionable column. So this works:

SELECT [date] AS thedate
FROM QofQ

Reply to this Comment

Exactly... in fact, the same thing would apply to a regular non-query-of-queries SQL statement.

Reply to this Comment

I've run into this error as well using the COUNT function. It only works when i have just the function in the SELECT list and use no alias which is worthless for my purposes.

Reply to this Comment

I got this exception now.
In case you're using var-es local struct, CF gives you couple of "new" exceptions:

Encountered "local.

and

Encountered "id. Incorrect Select List, Incorrect select column

This is the way out:

<cfset var local = structNew() >
<cfset local.query = something... >
<cfquery name="local.queryOfQueries" dbtype="query" >
SELECT [id],
[columnId]
FROM [local].query
WHERE [columnId] IN ( ...cfqueryparam list output...)
ORDER BY [id] ASC
</cfquery>

Those who work with MS SQL Server should recognize this syntax.

Reply to this Comment

Ben,

You consistently give excellent explanations to most of the problems I have. Thank you for your service to the CF community!

Reply to this Comment

I'm sure this is already a known fact but figured I'd mention it anyways just in case some unsuspecting soul tries it:

What works in QoQ:

select myQuery1.name, myQuery2.city
from myQuery1, myQuery2
where myQuery1.id = myQuery2.id

What won't work:

select qry1.name, qry2.city
from myQuery qry1, myQuery qry2
where qry1.id = qry2.id

Aliasing won't work, or I'm doing something wrong.

Reply to this Comment

I found this same issue when trying to use MID() in a cfquery of a query (PDF Solr Collection). I've been going nuts trying to find away around this problem all day. I need part of the date string in the URL to order my results.

  • <cfquery dbtype="query" datasource="search" >
  • SELECT KEYWORDS, RANK, RECORDSSEARCHED, SCORE, SUBJECT, SUMMARY, TITLE, TYPE, URL, MID(URL,21,4) AS ShortBy
  • FROM search
  • ORDER BY Shortby desc
  • </cfquery>
  •  
  • <cfoutput name="TestOutput" query="search">
  • <table>
  • <tr>
  • <td>#KEYWORDS#</td>
  • <td>#RANK#</td>
  • <td>#RECORDSSEARCHED#</td>
  • <td>#SCORE#</td>
  • <td>#SUBJECT#</td>
  • <td>#SUMMARY#</td>
  • <td>#TITLE#</td>
  • <td>#TYPE#</td>
  • <td>#URL#</td>
  • <td>#SB#</td>
  • </tr>
  • </table>

Thanks!

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.