Solved

Receiving error on ColdFusion query of query

Posted on 2013-11-25
6
473 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

810 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