Solved

Query of Queries in cfcomponent

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

920 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

16 Experts available now in Live!

Get 1:1 Help Now