Solved

Receiving error on ColdFusion query of query

Posted on 2013-11-25
6
477 Views
Last Modified: 2013-11-25
I have a query and a query of that query, something like the below, but I am receiving the following error. Could someone assist?

Query Of Queries syntax error.Encountered "1. Incorrect Select Statement, Expecting a 'FROM', but encountered '1' instead, A select statement should have a 'FROM' construct.

<cfquery name="firstQ" datasource="myds"  username="user1" password="pass1">
select ID, Column1, Column2
From My table
where ...
</cfquery>

<cfquery name="topfirstQ" dbtype="query">
select top 1 ID
From firstQ
</cfquery>
0
Comment
Question by:earwig75
  • 3
  • 2
6 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 39674881
remove "TOP 1" from the select (not allowed in QofQ)

Instead use maxRows in the parmeter

<cfquery name="topfirstQ" dbtype="query" maxRows =1>
   select  ID
     From firstQ
</cfquery>


Of course, there is no need for this at all, because to get the ID of the first record, you just take it...

<cfquery name="firstQ" datasource="myds"  username="user1" password="pass1">
select ID, Column1, Column2
From My table
where ...
</cfquery>
<cfset firstID = firstQ.ID>

Or you can use...

<cfset firstID = firstQ["ID"][1]>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39674988
(no points, gdemaria already provided the answer ...)

Just FYI, if you were going to grab the top 1 record/maxrRows=1, you must ORDER the results by something - an "ID", date, etc.... or else you're just grabbing an arbitrary record that isn't necessarily the one you want.
0
 

Author Comment

by:earwig75
ID: 39675243
Forgive me. I left something out of my initial question. I don't actually need the first record from the first query. I need the LAST one. In my real code I am trying to select top 1 and then: order by ID DESC

The first query gets a bunch of records, and then the 2nd query, I want the last record from that first one.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 39

Accepted Solution

by:
gdemaria earned 250 total points
ID: 39675317
To get the last one, you can do as you suggested and order by ID desc - provided of course, your definition of "last" record is the one with the largest ID


or you don't need Q of Q at all... just grad the ID from the last record:

<cfset firstID = firstQ["ID"][firstQ.recordCount]>
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 250 total points
ID: 39675338
Right, but remember you have to sort first . Otherwise the terms "first" and "last" don't mean anything :) If I'm understanding correctly, you want to sort the results by the largest ID first, then grab the largest one - correct? If yes, you could use the example gdemaria already provided but add an ORDER BY clause to it:

<cfquery name="topfirstQ" dbtype="query" maxRows="1">
     SELECT ID
     FROM   firstQ
     ORDER BY ID DESC
</cfquery>


That said ... it sounds like you just want the largest ID value from the query. If that's the case, you could simply use MAX:

<cfquery name="getLastID" dbtype="query" maxRows="1">
     SELECT  MAX(ID) AS LargestID
     FROM    firstQ
</cfquery>
<cfoutput>#getLastID.LargestID#</cfoutput>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39675357
or you don't need Q of Q at all... just grad the ID from the last record:
<cfset firstID = firstQ["ID"][firstQ.recordCount]>

Oops our responses collided...

EDIT:Yes, IF their first query is already sorted by ID DESC, then that's correct. A QoQ isn't needed.  (That wasn't clear from the original post). Otherwise, they do need some kind of QoQ to resort the results first - so they get the correct record, not an arbitrary value.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question