Solved

Query of Queries in cfcomponent

Posted on 2012-04-05
4
328 Views
Last Modified: 2012-06-27
I've successfully executed a Query of Queries on a cfml page where the code for each cfquery was in the header of that page.  Now I'm trying to move the two queries to a cfc to be accessed remotely.

My cfc has the following code (getOffices is a simplified dummy query for illustrative purposes -- the actual query queries several mySQL tables):

<cffunction name="getOffices" access="remote" output="no" returntype="query">
 <cfquery name="q">
  SELECT  "Brisbane" as city, "QLD" as state
  UNION
  SELECT  "Melbourne" as city, "Vic" as state
  UNION
  SELECT  "Sydney" as city, "NSW" as state
  UNION
  SELECT  "Cairns" as city, "QLD" as state
  UNION
  SELECT  "Geelong" as city, "Vic" as state
  UNION
  SELECT  "Newcastle" as city, "NSW" as state
  </cfquery>
 <cfreturn q>
</cffunction>

 <cffunction name="getStates" access="remote" output="no" returntype="query">
  <cfset theQuery = getOffices()>
  <cfquery dbtype="query" name="q">
   SELECT DISTINCT state 
   FROM #theQuery#
   ORDER BY state
  </cfquery>
  <cfreturn q>
 </cffunction>

Open in new window

When I try to invoke the getStates method from my cfml page, I get an error message:

Complex object types cannot be converted to simple values. The expression has requested a variable or an intermediate expression result as a simple value. However, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values.

The most likely cause of the error is that you tried to use a complex value as a simple one. For example, you tried to use a query variable in a cfif tag.
How do I get things to work?
0
Comment
Question by:ShanghaiD
  • 2
4 Comments
 
LVL 36

Accepted Solution

by:
SidFishes earned 500 total points
ID: 37811646
just change

 SELECT DISTINCT state
   FROM #theQuery#
   ORDER BY state

to

 SELECT DISTINCT state
   FROM theQuery
   ORDER BY state
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37812264
(no points. the question was already answered)

The reason #theQuery# doesn't work is because the QoQ table name must be a simple string.  By using # signs, CF evaluates #theQuery# first and passes the query object to the QoQ, instead of the name.

Also, to avoid threading problems you should var scope both the "thequery" and "q" variables - in both functions.
0
 

Author Comment

by:ShanghaiD
ID: 37814634
SidFishes:  Thank you!  I "thought" I had tried that (without the # #) before I posted my question (as well as other alternatives for the syntax) but clearly I had not.

_agx_: Thanks for the clear explanation of what was going wrong.  Also, I will follow your recommendation to var scope in future.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37814796
Welcome :)
0

Featured Post

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.

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 …
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 …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 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