Solved

Receiving error on ColdFusion query of query

Posted on 2013-11-25
6
469 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now