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?

Answer 1

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#">

Answer 2

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.

Answer 3

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.