sql - 我们可以在 ColdFusion Query-of-Query 中使用 "Case"

Question

我正在申请 case在查询的 ColdFusion 查询中，但它抛出了一个错误。

查询:

<cfquery name="qEmployees1" dbtype="query">
    select (
        case 
          when ISNUMERIC(u.userdefined)=1
          then right('00000000'+u.userdefined,8)
          else userdefined
        end
      ) as hello
    from all_employees
    order by hello ASC
</cfquery>

错误信息:

Encountered "when" at line 3, column 22. Was expecting one of: 
    "AND" ... "BETWEEN" ... "IN" ... "IS" ... "LIKE" ... "NOT" ... 
    "OR" ... ")" ... "=" ... "." ... "!=" ... "#" ... "<>" ... 
    ">" ... ">=" ... "<" ... "<=" ... "+" ... "-" ... "*" ... 
    "||" ... "/" ... "**" ... "(" ...

Answer 1

更新:

最初的建议不会起作用，因为它只查看一行。你真的需要遍历你的 all_employees记录集并将其应用于每一行。

如果您只是将结果输出到页面，您可能无需 QoQ 即可实现此目的。像这样:

<cfoutput>
    <cfloop query="all_employees">
     <cfif isNumeric(all_employees.userdefined)>
      #Right('00000000'&all_employees.userdefined,8)#
     <cfelse>
      #all_employees.userdefined#
     <cfif>
    </cfloop>
</cfoutput>

原答案:

这样的事情怎么样？:

<cfquery name="qEmployees1" dbtype="query">
SELECT 
<cfif isNumeric([all_employees].[u.userdefined])>
  right('00000000'+u.userdefined,8) 
<cfelse>
 u.userdefined
</cfif> AS hello
FROM all_employees
ORDER by hello
</cfquery>

我尚未对此进行测试，但我认为在这种情况下，SQL 列名称中的点符号不会正常工作。无论如何，我将它括在方括号中。