How to use functions in Queries on Queries?

Question

I want to implement something similar to IIF in the QoQ below. However it's giving me an error. Either I'm doing it wrong or it's just not possible. Hopefully, it's the former.

<cfquery dbtype="query">
select 
  lastname + IIF(Len(firstname) > 0, DE(", " & firstname), DE("")) as fullname 
from myquery
</cfquery>

I'm getting this error:

Query Of Queries syntax error.
Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct.

Any ideas on how to fix this problem?

You could always forget query of queries altogether. You could put your logic in your output code. Or, if you need it in the query, you can add a case statement to the original query, or, loop through the results and use QuerySetCell. — Dan Bracuk, Jun 25 '15 at 11:57
As mentioned, database queries offer much greater flexibility and power than a QoQ. While there are workarounds (in this specific case), unless there is a specific reason you need a QoQ, it is simpler to perform the concatenation in the original DB query. — Leigh, Jun 25 '15 at 12:37

score 8 · Accepted Answer · answered Jun 25 '15 at 08:17

Query of Queries only supports a small set of SQL functionality which does not include the case statement. However, you could use a union within your query of queries to achieve what you are after. Something like this:

<cfset q = QueryNew("firstname,lastname")>

<cfset queryAddRow(q)>
<cfset querySetCell(q, "firstname", "")>
<cfset querySetCell(q, "lastname", "Smith")>

<cfset queryAddRow(q)>
<cfset querySetCell(q, "firstname", "Joe")>
<cfset querySetCell(q, "lastname", "Bloggs")>


<cfquery name="r" dbtype="query">
    SELECT lastname + ', ' + firstname as fullname
    FROM q
    WHERE firstname <> ''

    UNION

    SELECT lastname as fullname
    FROM q
    WHERE firstname = ''

    ORDER BY fullname
</cfquery>

<cfdump var="#r#">

Pankaj · Answer 2 · 2015-06-25T08:46:07.307

First of all you cannot use ColdFusion functions with SQL and vice versa, whether you are using normal query or query of queries.

Other thing that I would like to point out (as @snackboy already mentioned) is whenever you use ColdFusion function in generating dynamic queries, you need to put it in pound sign (#).

For what you are trying to achieve is done using CASE WHEN. But that is not supported by query of query. So you need to perform CASE WHEN in the actual query itself.

snackboy · Answer 3 · 2015-06-25T20:08:03.640

0

But as a workaround, you could do the following:

<cfquery dbtype="query" name="myQueryResult">
    select lastname, firstname, lastname as fullname from myquery
</cfquery>

<cfoutput query="myQueryResult">
    <cfif len(firstname) gt 0>
         <cfset myQueryResult.fullname = lastname & ', ' & firstname>
    </cfif>
</cfoutput>

Sorry this doesn't directly answer the question. I may have time later to come back to this question. I do know that a query of a query is fairly limited in CF.

edited Jun 25 '15 at 20:08

answered Jun 25 '15 at 05:46

snackboy

624
3
12

I'm not the one who downvoted this but I did try the iif stuff with octothorps. The error message changed to unknown variable. – Dan Bracuk Jun 25 '15 at 14:11
Not sure why someone would down vote as it is a viable solution to the problem as stated. – snackboy Jun 25 '15 at 14:14
1

I did not down vote it, but perhaps it was due to the initial comments. QoQ's use a very rudimentary form of SQL, not pure CFML. So conceptually they behave like a standard database query. As with a db query, you cannot apply CFML functions to database objects (ie the base query object). Also, while "+" is not a valid concatentation operator in CFML, [it is a valid operator in a QoQ](https://wikidocs.adobe.com/wiki/display/coldfusionen/Query+of+Queries+user+guide). – Leigh Jun 25 '15 at 16:06

How to use functions in Queries on Queries?

3 Answers3