coldfusion - 如何在查询查询中使用函数？

Question

我想在下面的 QoQ 中实现类似于 IIF 的东西。但是它给了我一个错误。要么我做错了，要么就是不可能。希望是前者。

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

我收到此错误:

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

关于如何解决这个问题的任何想法？

Answer 1

Query of Queries 仅支持一小组 SQL 功能，其中不包括 case 语句。但是，您可以在查询查询中使用联合来实现您的目标。像这样的东西:

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