How do I use features in queries on demand?
I want to implement something similar to IIF in QoQ below. However, this is giving me an error. Either I'm doing it wrong, or it's just not possible. Hopefully this is the first one.
<cfquery dbtype="query">
select
lastname + IIF(Len(firstname) > 0, DE(", " & firstname), DE("")) as fullname
from myquery
</cfquery>
I am getting this error:
Query Of Queries syntax error.
Encounted "(invalid select statement expecting 'FROM' but colliding with '(' instead, the select statement must have a FROM clause.
Any ideas on how to fix this issue?
source to share
Query of Queries only supports a small set of SQL functionality that does not include a case statement. However, you can use concatenation in a query query to achieve what you need. Something like that:
<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#">
source to share
In the first place, you cannot use ColdFusion features with SQL and vice versa, whether you are using a regular query or a query query.
Another thing I would like to point out (as mentioned by @snackboy), when you use a ColdFusion function when generating dynamic queries, you need to put it in the pound sign ( #
).
What you are trying to achieve is accomplished with CASE WHEN
. But this is not supported by the request request. Therefore, you need to execute CASE WHEN
in the actual query itself.
source to share
But as a workaround, you can 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 does not directly answer the question. Perhaps I will have time to return to this question later. I know the query query is pretty limited in CF.
source to share