在Crystal Reports中将SQL语句转换为SQL表达式字段 [英] Converting SQL statement to SQL expression field in Crystal Reports

问题描述

我有一条SQL语句可提取所需的数据，但我在Crystal Reports中无法正确使用语法。

I have a SQL statement that pulls data I need but I can't get the syntax right in Crystal Reports.

此语句在SQL中有效：

This statement works in SQL:

SELECT
    max([meter_reading])
FROM [Forefront].[dbo].[EC_METER_HISTORY_MC]
WHERE [Meter_Number] = '1' AND [Transaction_Date] < '20130101'
GROUP BY 
    [Company_Code], [Equipment_Code], [Meter_Number]

这是我在水晶中将其更改为的格式，但是我无法获得正确的语法。

This is what I changed it to in crystal but I can't get the right syntax.

SELECT     
   Maximum({EC_METER_HISTORY_MC.meter_reading}) 
FROM [EC_METER_HISTORY_MC] 
WHERE {EC_METER_HISTORY_MC.Meter_Number} = '1' 
AND {EC_METER_HISTORY_MC.Transaction_Date}  <  {1?Startdate}
GROUP BY {EC_METER_HISTORY_MC.Company_Code}
,{EC_METER_HISTORY_MC.Equipment_Code}
,{EC_METER_HISTORY_MC.Meter_Number}

推荐答案

您的第一步应该是阅读SQL表达式在Crystal中的工作方式。 这里是帮助您入门的好链接。

Your first step should be reading up on how SQL Expressions work in Crystal. Here is a good link to get you started.

您的一些问题包括：

1. 使用a参数字段。 SQL表达式与CR
   参数不兼容，因此不能在其中使用。


2. SQL表达式只能返回报表每一行的标量值。这意味着您对GROUP BY的
   使用没有任何目的。


3. 使用花括号表示您在主报告中引用了这些字段查询，而不是您尝试使用此表达式创建的子查询。

1. Using a parameter field. SQL Expressions are not compatible with CR parameters and cannot be used in them.
2. SQL Expressions can only return scalar values per row of your report. That means that your use of GROUP BY doesn't serve any purpose.
3. Your use of curly braces means that you're referencing those fields in the main report query instead of in the subquery you're trying to create with this expression.

这是一个简化的示例，可以找到特定仪表的最大仪表读数（对于Oracle，因为这是我所知道的，并且您未指定要使用的数据库）：

Here's a simplified example that would find the max meter reading of a particular meter (for Oracle since that's what I know and you didn't specify which DB you're using):

case when {EC_METER_HISTORY_MC.Meter_Number} is null then null
else (select max(Meter_Reading)
      from EC_METER_HISTORY_MC
      where Meter_Number={EC_METER_HISTORY_MC.Meter_Number} --filter by the meter number from main query
       and Transaction_Date < Current_Date) --filter by some date. CAN'T use parameter here.
end

这篇关于在Crystal Reports中将SQL语句转换为SQL表达式字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！