[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Query of Queries in cfcomponent

Posted on 2012-04-05
4
Medium Priority
?
334 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
[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
  • 2
4 Comments
 
LVL 36

Accepted Solution

by:
SidFishes earned 2000 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 Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
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 …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

649 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