我们能在ColdFusion Query-of-Query中使用"Case"语句吗？

Question

我们能在ColdFusion Query-of-Query中使用"Case"语句吗？

sqlcoldfusioncoldfusion-2016qoq

4

我正在ColdFusion query of query中应用case，但出现错误。

查询：

<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" ... ")" ... "=" ... "." ... "!=" ... "#" ... "<>" ... 
    ">" ... ">=" ... "<" ... "<=" ... "+" ... "-" ... "*" ... 
    "||" ... "/" ... "**" ... "(" ...

- Techleadz Team

2

查询查询SQL非常有限。相反，使用<cfif>标签进行条件语句。 - volume one

@volumeone 我们如何使用<cfif>代替case？ - Techleadz Team

all_employees.userdefined 的数据类型是什么？我猜测它是 varchar？ - Shawn

再次查看后，我同意下面@Ageax的评论。我认为cfif在这里不会做你认为它在做的事情。由于这是针对CF2016的，我更改了我的答案，包括一些新选项，以消除QoQ并复制所尝试的功能。 - Shawn

5个回答

1

编辑：

我考虑了一下并决定将其改为实际答案。由于您正在使用CF2016+，因此可以访问CF提供的一些更现代的功能。首先，查询查询是一个很好的工具，但它可能非常慢。特别是对于较低的记录计数。然后，如果基本查询中有大量记录，则可能会耗尽服务器的内存，因为它是一个内存操作。我们可以在不需要QoQ的情况下实现我们的目标。

我们可以通过一些新的CF函数来复制您要查找的功能。 filter，each和sort都适用于查询对象。这些是这些的成员函数版本，但我认为它们看起来更清晰。此外，我使用了cfscript语法。

我主要重用了原始的CFSCript查询（all_employees），该查询创建查询对象，但我添加了一个f列，其中包含要过滤的文本。

all_employees = QueryNew("userdefined,hello,f", "varchar,varchar,varchar",
    [
      ["test","pure text","takeMe"],
      ["2","number as varchar","takeMe"],
      ["03","leading zero","takeMe"],
      [" 4 ","leading and trailing spaces","takeMe"],
      ["5        ","extra trailing spaces","takeMe"],
      ["        6","extra leading spaces","takeMe"],
      ["aasdfadsf","adsfasdfasd","dontTakeMe"],
      ["165e73","scientific notation","takeMe"],
      ["1.5","decimal","takeMe"],
      ["1,5","comma-delimited (or non-US decimal)","takeMe"],
      ["1.0","valid decimal","takeMe"],
      ["1.","invalid decimal","takeMe"],
      ["1,000","number with comma","takeMe"]

    ]
) ;

原始的基础查询没有WHERE子句，因此在初始结果上没有进行额外的过滤。但是如果需要，我们可以使用QueryFilter或.filter来复制它。

filt = all_employees.filter( function(whereclause){ return ( whereclause.f == "takeMe"); } ) ;

这个函数接受 all_employees 查询并应用一个函数，该函数仅返回符合我们要求的行。因此，任何查询中的行都满足 f == "takeMe" 的条件。这就像查询中的 WHERE f = 'takeMe'。将新过滤结果设置为新的查询对象 filt。

然后，我们可以使用 QueryEach 或 .each 遍历我们的新过滤查询的每一行以修改我们需要的内容。在这种情况下，我们正在构建一个新数组来存储我们想要的值。使用 for/in 循环可能会更快；我还没有测试过。

filt.each(
        function(r) {
            retval.append(
                ISNUMERIC(r.userDefined) ? right("00000000"&ltrim(rtrim((r.userdefined))),8) : r.userDefined
            ) ;
        }
    )  ;

现在我们有了一个包含所需结果的新数组，原始的 QoQ 希望对这些结果进行排序。我们可以使用 ArraySort 或 .sort 来实现。

retval.sort("textnocase") ;

在我的测试中，CF2016似乎将retval.sort()作为布尔值传递，并没有返回排序后的数组，但CF2018则有。这是预期的行为，因为返回类型在CF2018中已更改。无论如何，两者都会对retval数组进行排序，因此当我们转储retval数组时，它按所选顺序排列。

而且正如我一直建议的那样，在您的系统上使用您的数据进行负载测试。就像我说的，这只是您尝试完成任务的其中一种方式。还有其他可能更快的方法。

https://cffiddle.org/app/file?filepath=dedd219b-6b27-451d-972a-7af75c25d897/54e5559a-b42e-4bf6-b19b-075bfd17bde2/67c0856d-bdb3-4c92-82ea-840e6b8b0214.cfm

（CF2018）> https://trycf.com/gist/2a3762dabf10ad695a925d2bc8e55b09/acf2018?theme=monokai

https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/queryfilter.html

https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/queryeach.html

https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-a-b/arraysort.html

翻译：

这更像是一条评论而不是答案，但对于评论来说太长了。

我想提到一些需要注意的事情。

首先，ColdFusion的isNumeric()有时会产生意外的结果。它并没有真正检查一个值是否为数字。它检查一个字符串是否可以转换为数字。因此，有各种各样的值，isNumeric()会将其视为numeric。例如：1e3是科学计数法表示1000。isNumeric("1e3")将返回true。

我的第二个建议是如何处理“数字”值中的前导和尾随空格，例如：" 4 "。 isNumeric()将返回true，但当您附加并修剪最终值时，它将变为"000000 4"。我的建议是在列周围使用val()或ltrim(rtrim())来处理这些内容。 val()将其减少为基本数字（" 1.0 " >> "1"），但ltrim(rtrim())将保留数字但去除空格（" 1.0 " >> "1.0"），并且保留“科学计数法”值（" 1e3 " >> "1e3"）。两者仍然错过了1,000，因此如果这是一个问题，则需要处理它。但是，您使用的方法完全取决于数据包含的值。数字验证并不总是像看起来应该那么容易。

我一直坚信GIGO——垃圾进，垃圾出。我认为基本的数据清洗是我的工作的一部分。但如果它是极端或经常性的，我会告诉数据来源去修复它，否则他们的东西就无法正常工作。当涉及到数据时，不可能考虑到所有可能性，但我们可以检查常见的期望。白名单比黑名单更容易。

<cfscript>
all_employees = QueryNew("userdefined,hello", "varchar,varchar",
    [
      ["test","pure text"],
      ["2","number as varchar"],
      ["03","leading zero"],
      [" 4 ","leading and trailing spaces"],
      ["5        ","extra trailing spaces"],
      ["        6","extra leading spaces"],
      ["165e73","scientific notation"],
      ["1.5","decimal"],
      ["1,5","comma-delimited (or non-US decimal)"],
      ["1.0","valid decimal"],
      ["1.","invalid decimal"],
      ["1,000","number with comma"]

    ]
)

//writedump(all_employees) ;

retval = [] ;

for (r in all_employees) {
    retval.append(
        {
          "1 - RowInput"   : r.userdefined.replace(" ","*","all") , // Replace space with * for output visibility.
          "2 - IsNumeric?" : ISNUMERIC(r.userdefined) ,
          "3 - FirstOutput": ( ISNUMERIC(r.userDefined) ? right("00000000"&r.userdefined,8) : r.userDefined ) ,
          "4 - ValOutput"  : ( ISNUMERIC(r.userDefined) ? right("00000000"&val(r.userdefined),8) : r.userDefined ) ,
          "5 - TrimOutput"  : ( ISNUMERIC(r.userDefined) ? right("00000000"&ltrim(rtrim((r.userdefined))),8) : r.userDefined )
        } 
    ) ;
}

writeDump(retval) ;
</cfscript>

https://trycf.com/gist/03164081321977462f8e9e4916476ed3/acf2018?theme=monokai

- Shawn

1

如果有人决定尝试下面的QoQ，需要注意的一件非常重要的事情是，即使它没有错误地执行，它也不会像CASE那样执行相同的操作。 CASE语句对表中每行的值应用逻辑-单独地。在QoQ版本中，CFIF表达式不会对查询中的所有值进行操作。它只检查第1行中的值，然后将该决策应用于查询中的所有行。

请注意，下面的QoQ（错误地）报告所有值都是数字吗？而数据库查询（正确地）报告了“数字”和“非数字”值的混合。因此，QoQ代码不等同于CASE。 TestTable数据：

id  userDefined
1   22
2   AA
3   BB
4   CC

数据库查询：

   SELECT CASE
            WHEN ISNUMERIC(userDefined)=1 THEN 'Number: '+ userDefined
            ELSE 'Not a number: ' + userDefined
        END AS TheColumnAlias
   FROM TestTable
   ORDER BY ID ASC

数据库查询结果：

QoQ。

<cfquery name="qQueryOfQuery" dbtype="query">
  SELECT 
      <cfif isNumeric(qDatabaseQuery2.userDefined)>
         'Number: '+ userDefined
      <cfelse>
         'Not a number: ' + userDefined
      </cfif>
      AS TheColumnAlias
   FROM qDatabaseQuery2
   ORDER by ID
</cfquery>

QoQ结果

- SOS

0

你到底想做什么？请分享一些关于你的帖子目标的上下文。

在我看来，你的查询可能没有正确格式化。它将被评估为类似于：

    select ( 0000000099
      ) as hello
    from all_employees
    order by hello ASC

试着这样做。在这里放置一个<cfabort>，然后让我知道当你运行它时屏幕上产生了什么查询。

<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
<cfabort>
</cfquery>

- TJK-all-the-way

你确定cfabort放置的位置正确吗？此外，你确定QofQ支持case结构吗？ - Dan Bracuk

是的，请记住 QoQ 不同于数据库查询。虽然您的 DBMS（SQL Server、MySQL 等）可能支持 CASE，但 Adobe QoQ 不支持。QoQ 的功能非常有限。 - SOS

-1

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

这是一个无需语法的答案，感谢@volumeone

- Techleadz Team

我改口了。从技术上讲，在某些版本的ACF下它可以无错误运行，但是...与dbms CASE不返回相同的结果。 - SOS

如果你只是输出到页面，甚至可能不需要使用QoQ。我已经更新了我的回答。 - volume one

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- volume one · Accepted Answer

更新：

由于原始建议只查看单个行，因此原始建议不起作用。实际上，您需要循环遍历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>

Original Answer:

How about something like this?:

<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列名中使用点符号表示法在这种情况下会正常工作。无论如何，我还是将其放在方括号中。