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