SQL Crystal Reports中SQL语句转换为SQL表达式字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14609589/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting SQL statement to SQL expression field in Crystal Reports
提问by user2022883
I have a SQL statement that pulls data I need but I can't get the syntax right in Crystal Reports.
我有一个 SQL 语句可以提取我需要的数据，但我无法在 Crystal Reports 中获得正确的语法。
This statement works in SQL:
此语句适用于 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}
回答by Ryan
Your first step should be reading up on how SQL Expressions work in Crystal. Here is a good link to get you started.
您的第一步应该是阅读 SQL 表达式在 Crystal 中的工作原理。这是一个很好的链接，可以帮助您入门。
A few of your problems include:
您的一些问题包括：
- Using a parameter field. SQL Expressions are not compatible with CR parameters and cannot be used in them.
- 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.
- Your use of curly braces means that you're referencing those fields in the main report queryinstead of in the subquery you're trying to create with this expression.
- 使用参数字段。SQL 表达式与 CR 参数不兼容，不能在其中使用。
- SQL 表达式只能返回报告的每一行的标量值。这意味着您使用 GROUP BY 没有任何用途。
- 您使用大括号意味着您在主报表查询中而不是在您尝试使用此表达式创建的子查询中引用这些字段。
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):
这是一个简化的示例，可以找到特定仪表的最大仪表读数（对于 Oracle，因为这是我所知道的，并且您没有指定您正在使用的数据库）：
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
回答by craig
You can't use parameter fields in a SQL Expression, sadly. Perhaps you can correlate the Transaction_Dateto a table in the main query. Otherwise, I would suggest using a Command.
遗憾的是，您不能在 SQL 表达式中使用参数字段。也许您可以将Transaction_Date与主查询中的表相关联。否则，我建议使用命令。
You have two options for the Command:
命令有两个选项：
- Use a single Command object as the data source for the whole report--which involves (potentially) a fair amount of rework.
- Add a Command to the existing table set (in the Database 'Expert'). Link it to other tables as desired. This will perform a second
SELECTand join the results in memory (WhileReadingRecords, if I'm not mistaken). The slight performance hit may we worth the benefit.
- 使用单个 Command 对象作为整个报告的数据源——这涉及（可能）大量的返工。
- 将命令添加到现有表集（在数据库“专家”中）。根据需要将其链接到其他表。这将执行一秒钟
SELECT并将结果加入内存（WhileReadingRecords，如果我没记错的话）。轻微的性能下降可能值得我们受益。
