Solved

Query of Queries in cfcomponent

Posted on 2012-04-05
4
325 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

705 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

12 Experts available now in Live!

Get 1:1 Help Now