Solved

Receiving error on ColdFusion query of query

Posted on 2013-11-25
6
485 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 
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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

626 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