创建查询

• 查询使您能够快速显示和格式化 SAP Business One 公司数据库中的数据。可以通过多种方式使用查询，例如：
  • 生成可反复运行的特定报表。这些查询称为用户查询。
  • 在 SAP Business One 中创建用户定义的警报和审批流程。查询可以检查预定义警报或标准审批程序未涵盖的条件。
  • 填充字段的内容，包括用户定义的字段。查询将作为用户定义的值（格式化搜索）添加到字段中，并可以从查询结果中填充字段值。
  • 在仪表盘和 KPI 中显示分析的动态信息
  • 验证数据迁移期间导入到表或字段中的值。
• 您应该明白，查询工具并不是为了生成复杂、完全格式化的报表或打印格式而设计的；您应将 Crystal Reports 用于这些目的。不允许使用查询工具插入、更新或删除标准表字段。

为了运行查询，您需要了解 SAP Business One 表和字段的名称。启用系统信息时，将显示此信息。

一个对象可以跨 SAP Business One 数据库中的多个表。

例如，业务伙伴主数据对象保存在数据库的多个表中，包括抬头表 OCRD。存在相关子表，例如 OCPR、CRD1、OSLP 等。这些子表包含您在业务伙伴记录的各个标签上看到的数据。

在 SAP Business One 中，表名称的长度为 4 个字符。

要开发 SQL 查询，您需要了解数据库表和字段名称以供选择。

在 SAP Business One 中打开对象时，可以通过从视图菜单中启用系统信息来查找字段的表名称。您还可以使用键盘快捷键 Ctrl + Shift + I。

然后，将鼠标悬停在对象中的字段上时，该字段的相关表信息将显示在窗口底部的状态栏中。

现在，您可以在 SQL 查询中输入表名称和字段名称。

显示有关字段的其他信息，例如字段的最大长度。

业务伙伴主数据对象将显示在幻灯片中。如果将鼠标悬停在名称字段上，您将看到表为 OCRD，字段名称为 CardName。

销售和采购单据（如订单、报价和发票）都具有相同的结构，并基于两个主要对象：

• 凭证抬头部分的 凭证 对象
• Document_Lines 对象，用于文档的矩阵或行部分

销售订单等单据类型跨多个表。每个凭证类型的表将不同，因此采购订单的表集与销售订单的表集不同。

例如，销售订单使用数据库表 ORDR 作为抬头，使用表 RDR1 作为行。

• 除表和字段名称外，系统信息还显示抬头字段的物料编号以及基于行的字段的物料、列和行编号。
• 例如，在销售订单中，卡代码字段的物料编号为 4。不显示 CardCode 字段的列编号，因为这是表 ORDR 中的抬头字段，并且所有抬头字段的列编号为 0。
• 如果打开其他营销单据，您将看到卡代码字段始终为 4。
• 为什么这一点很重要？字段的物料编号在具有相同结构的所有凭证类型中通用，例如销售和采购单据。在这些凭证中，物料编号相同，但表名称不同。使用物料编号而不是字段名称，您可以通过参考相同的物料编号来编写可跨多个单据类型的查询。
• 在幻灯片的下方屏幕截图中，您可以看到销售订单单据矩阵中的 ItemCode 字段的物料编号为 38，列编号为 1。行编号 1 表示单据中的第一行（即行编号从 1 开始）。
• 您会注意到，行字段的物料编号和列编号在类似的单据类型中相同，例如，在所有销售和采购单据中，ItemCode 的物料编号为 38，列编号为 1。

• 您会发现某些字段不显示表和字段名称，包括单价和计算字段，例如总计和税收。这些字段显示在与货币符号连接的凭证中，而在数据库中，金额存储时不包含货币符号。
• 您仍然可以在查询中使用这些字段。您可以使用物料编号来参考字段，也可以从数据库表参考中获取数据库字段名称。

您可以在 SAP Business One SDK > 帮助文件夹中找到数据库表参考文件 REFDB，或者，如果已安装数据传输工作台，则可以从帮助菜单中打开它。

除对象的字段名称外，参考文件还提供：

• 描述
• 字段类型（例如 Int、VarChar、Numeric、Text 等）。
• 字段的最大长度
• 如果字段是外键，则存在相关表的链接
• 缺省值（如果存在）
• 字段值约束

SAP 提供两种查询工具帮助您开发查询语法。

• 用于创建和管理用户查询的工具位于客户端应用程序顶部菜单栏中的 工具 菜单下。
• SAP Business One 提供了两种工具：查询生成器和查询向导，可帮助您使用结构化查询语言 (SQL) 创建查询。SQL 是一组标准化命令，用于访问和格式化关系数据库中的数据。这两个工具最终会产生相同的结果，它主要是您自己对使用哪种工具的一个偏好。

• 顾名思义，查询向导将逐步指导您完成创建查询的过程，而无需编写 SQL 命令和语法。如果您对 SQL 没有过多经验，则应使用此工具。查询向导是一个多步向导。
• 要从查询向导运行查询，请在最后一步中选择完成按钮。
• 生成的查询和查询结果显示在查询预览窗口中。

• 在向导的第一步中，按 Tab 查看数据库表列表并选择所需表（显示相关表并可以选择）。
• 然后，在下一步中按 Tab 键查看和选择字段。显示表中的所有字段，您可以在查找字段中输入部分名称。双击字段将其选中以进行查询。在向导步骤中选择单独行中的各个字段。
• 系统在后台生成 SQL 语句，因此不需要精确的 SQL 语法知识。但是，由于此操作由向导完成，因此生成查询需要执行多个步骤。
• 如果您对 SAP Business One 表不熟悉以及如何相互关联，您会发现查询向导更易于使用。选择表时，查询向导将显示与所选表相关的所有表，允许您从多个表中选择数据。如果为查询选择多个表，向导将负责表联接。
• 生成的查询语法显示在幻灯片中。

• 第二个工具"查询生成器"允许您在单个屏幕中创建 SQL。如果您有一些基本的 SQL 知识，您会发现查询生成器比查询向导快得多。
• 选择表，然后选择字段，查询将显示在窗口的右侧。
• 要在查询生成器中运行汇编查询，请选择执行按钮。
• 查询结果显示在查询预览窗口中。

• 要生成查询，请在窗口左上角的文本框中输入表名称，或在此框中按 Tab 键以查看完整的表列表。
• 再次按 Tab 以查看表中所有字段的列表。双击以从显示的清单中选择每个字段。查询的元素已构建并显示在工具窗口的右侧。
• 与查询向导一样，当您为一个查询选择多个表时，查询生成器将自动提供内连接。尽管它不显示与所选表相关的表清单，但以粗体显示对相关表重要的字段。您可以将粗体字段拖放到表选择列中，查询生成器将打开相关表，允许您从该表中选择字段。

查询预览 窗口显示查询语法和结果

内置查询工具生成的 SQL 语法取决于基础数据库，即 SQL Server 数据库或 SAP HANA。但是，结果将相同。

在查询预览窗口中，您可以选择编辑构建的查询。单击"铅笔"图标编辑查询语法。在编辑模式下，您可以从头开始编写并运行自己的查询。

查询预览窗口还允许您保存查询以供重用。本课程稍后将对此进行讨论。

这两种查询工具可帮助您开发查询，但您还需要基本了解组成 SQL 查询语法的元素。

查询或基础 SQL 语句包含幻灯片中列出的一个或多个基本元素：

• 字段选择。还可以指定显示两个字段的加、减、乘或除结果的计算字段。
• 选择条件（where 子句）
• 排序顺序（order by 子句）
• 分组和汇总（group by 子句）

屏幕右上方显示的示例查询将显示上周添加的未清采购订单的信息。查询从采购订单表 OPOR 中选择数据。显示查询的结果，即数据库的简单快照。不显示行总计，但您可以通过按住 Ctrl 键并使用鼠标单击两次来将其添加到报表中。

请注意，SQL 不区分大小写，因此命令不必大写。对于包含小写字母的表名称和字段名称，在 SAP HANA 语法中，需要在字段名称两侧添加双引号。

本课程中显示的所有查询示例均适用于 SAP HANA。有关 SAP HANA 的 SQL 语法差异列表，请参阅文档 SAP HANA 数据库中的 SQL 最佳实践

• 可选的 Where 子句允许您仅过滤满足指定条件的记录。尽管查询工具将帮助您装配查询的基本元素，但您需要一些有关 SQL 语法的知识才能完成 Where 子句。
• 在 Where 子句中，可以包括：
  • 固定条件作为比较。例如，在我们的示例查询中，将仅显示未清（凭证状态 'O'）的采购订单。
  • 计算和函数。在示例查询中，我们已添加计算，以仅包括过去 7 天内过账的采购订单。我们将过账日期值 (DocDate) 与当前日期 - 7 进行匹配。函数 ADD_DAYS 用于计算当前日期减去 7。
  • 变量。变量指定为 [%0]、[%1]、[%2] 等。包含变量时，查询运行时将提示用户输入值作为参数。
• AND 和 OR 运算符可用于链接多个条件。在示例查询中，我们使用 AND 运算符，因为必须满足这两个条件。

注意：显示的示例查询使用 DocStatus 字段作为条件。在数据库中，此字段具有允许值（约束）的固定清单。要查找字段的可能值（如 DocStatus），您有两个选项：

• 对表运行查询并选择字段名称。在结果中，您将看到数据库中存储的可能值。
• 有关允许值（约束）的列表，请参阅数据库表参考。

• 变量使用户可以在运行查询时灵活地更改参数。
• 变量仅在方括号中定义，其中 %0 作为第一个变量，%1 是第二个变量，依此类推。
• 在此示例中，过账日期 DocDate 用作过滤器，但我们添加了变量条件。当用户运行查询时，系统会提示用户输入过账日期。用户在 Where 子句中输入的日期与采购订单中的过账日期进行比较。只有过账数据大于参数日期的记录，才会包含在查询结果中。

• 通过向查询中添加 Order By 子句，可以选择对查询结果进行排序。
• 默认情况下，将使用指定字段以升序 (ASC) 对结果进行排序。在此示例中，结果行（采购订单）将按过账日期 (DocDate) 排序。
• 可以通过添加关键字 DESC 按降序进行排序
• 可以按多个字段进行排序，这些字段可以是 select 子句的一部分，也可以是任何查询表中的其他字段。

• 可选的 Group By 子句允许您显示按指定字段（例如，按业务伙伴）分组或汇总的查询结果。在我们的示例中，已重写原始查询以使用 Group By 元素，并显示在幻灯片上的原始查询旁边。
• 使用分组依据字段或字段作为通用值，将分组结果收集到集中。
• Group by 通常与数学函数（如 Count 或 Sum）结合使用。在示例查询中，Count 函数计算采购订单的数量，SUM 函数计算每个采购订单的单据总计。
• 所选字段根据 Group by 子句中指定的字段进行合并，因此在示例查询中，结果按供应商 (CardCode) 合并，并且查询为每个供应商显示一个合并行，其中包含供应商所有未清采购订单的计数和总金额。
• 请注意，我们需要为计数和汇总的字段提供列标题，因为这些字段的列标题不在数据库中。
• 使用 Group by 子句时，在 Select 语句中使用的字段也必须出现在 Group By 子句或聚合函数（Count 或 SUM）中。为此，我们在 Group by 子句中指定了 CardName 字段。

• 在查询中指定多个表时，通常需要在每个表之间创建连接。当您为查询选择多个表时，查询向导和查询生成器工具会自动提供内连接，从而使您轻松实现此目的。例如，如果选择采购订单表 (OPOR) 并选择业务伙伴主数据表 (OCRD)，则查询工具将使用两个表通用的业务伙伴代码链接这些表。
• 查询工具自动为每个表添加别名，例如 T0、T1 等。如果查询引用多个表，这有助于识别字段的相关表。
• 选择表时，查询向导将显示与所选表相关的所有表。在查询生成器中，以粗体显示对相关表重要的字段。您可以将粗体字段拖放到表选择列中，查询生成器将打开相关表，允许您从该表中选择字段。

运行查询时，报表列的标题取自数据库列名称。要更改查询报表中的列标题：

• 在查询向导中，只需在向导屏幕的标题列中输入所需文本。
• 在查询生成器中，使用"as"关键字，后跟双引号的新标题文本。直接在"选择"框中输入此文本。

• 在许多情况下，查询需要参考活动窗口中的字段。在示例中，活动窗口由表单或单据顶部的蓝线标识。
• 这适用于审批流程中使用的查询以及用户定义的值。在这两种情况下，用户在活动窗口中处理单据时运行查询。
• 如果查询需要参考活动窗口而非数据库中的字段，则必须在表和字段名称两侧包含 $ 符号并放置方括号，以指示该字段在活动窗口中。
• 在显示的示例中，您已将查询作为用户定义的值添加到销售订单中的字段。查询将从主数据记录中获取客户的科目余额，并在销售订单的用户定义字段中显示余额。由于用户正在处理销售订单，因此销售订单是活动窗口，因此对 CardCode 的参考包括特殊语法。该查询将活动窗口中的 CardCode 与主数据中的 CardCode 相匹配。
• 本示例中的主数据记录不在活动窗口中。从数据库访问的字段（例如主数据记录中的余额字段）不需要 $ 符号。
• 有关查询和审批流程的详细信息，请参阅审批流程课程。有关查询和用户定义值的详细信息，请参阅用户定义值课程。

• 参考活动窗口中的字段时，可以使用表和字段名称语法或物料和列编号语法。
• 幻灯片中的示例是审批流程中使用的查询。如果将表和字段名称语法用于活动窗口，则查询将包含表名称，因此只能与单个单据类型一起使用。在这种情况下，由于 OPOR 表显式包含在查询中，因此只能运行查询来审批采购订单。

注意：审批流程中使用的查询始终需要选择真实结果。在 SAP HANA SQL 中，如果语句中没有 FROM 子句，请使用 FROM DUMMY。

• 如果改用物料编号和列语法，则查询可与类似结构的多个单据类型一起使用。例如，此查询可用于在审批模板中选择多个销售和采购营销单据的审批流程。
• 系统使用字段的索引和列编号，唯一标识凭证的每个字段。
• 使用物料和列语法参考抬头字段时，请将列编号设置为 0。
• 字段以字符串形式获取，因此，如果您需要其他格式，需要在列编号后指定格式：
  • 0 - 缺省字符串格式
  • 数字 - 以数字形式返回结果，可用于计算或比较运算符。在幻灯片示例中，将结果与 500 进行比较。由于 DocTotal 字段同时保留金额和货币符号，因此在此指定数字将提取金额。
  • 货币 - 返回包含金额和货币符号的字段中的货币符号，例如 DocTotal。
  • 日期 - 仅应在字段为日期字段时使用。日期的返回格式用于计算或比较运算符。

创建查询后，您可能希望将其保存以供重用。

• 可以从查询预览窗口保存查询。保存查询时，必须选择类别。为您提供常规类别，但如果您计划添加多个用户查询，则应创建附加类别来管理查询。
• 您保存的查询作为用户查询进行查找。要运行已保存的用户查询，请选择 SAP Business One 中的工具菜单，然后选择类别和查询名称。

• 顾名思义，查询管理器允许您管理您保存的用户查询。
• 您可以按类别保存并组织查询。还可以删除查询。
• 系统类别用于 SAP 提供的查询。"常规"类别用于保存用户查询，并且您可以创建附加类别。
• 要创建新类别，请选择管理类别按钮。然后选择添加以添加新类别并保存查询。在本示例中，我们将创建名为采购的新类别。

• 创建新类别时，必须将其分配到至少一个查询权限组，否则将无法保存新类别。
• 只有有权访问查询权限组的用户才能运行为该类别保存的查询。这提供了一种控制哪些用户可以访问和运行已保存的用户查询的方法。

让我们看一下此处提供的示例：

• 您可以看到查询权限组 1 和 2 已分配到查询类别销售。查询权限组 2 和 3 已分配到类别采购。
• 用户 Bill 和 Donna 已分配到查询权限组 1。
• 用户 Sophie 和 Tim 被分配到了查询权限组 2。
• Julie 和 Juan 被分配到查询权限组 3。
• 因此，Bill 和 Donna 可以通过其与查询权限组 1 的关联来运行销售类别中保存的所有查询。Sophie 和 Tim 可以运行销售类别和采购类别中保存的所有查询，因为这两个类别都与查询权限组 2 相关联。Julie 和 Juan 可以运行与查询权限组 3 相关联的采购类别中保存的所有查询。

在常规权限窗口中，为用户授予查询权限组的权限。您可以在报表 > 查询生成器下找到此权限。您可以看到 15 个查询权限 - 已保存查询 - 组编号 1 -15。这些是为您提供开箱即用的。您可以在查询管理器中创建附加查询权限组。

在幻灯片示例中，授予用户 bob 只读查询权限组 1 和 2 的权限。同一用户还被授予名为"警报的用户查询"组的完全权限，该组是已添加到列表中的新权限组。

由于在类别级别授予权限，因此用户 bob 可以运行保存在与这些所选权限组之一相关联的类别中的任何查询。

如果希望安全访问已保存的用户查询，则需要仔细计划查询类别和查询权限组。

请注意，查询权限组与用于将常规权限分配给多个用户的权限用户组不同。要了解有关常规权限的详细信息，请参阅相关课程常规权限和用户组。您还可以将权限同时分配给用户组中的多个用户。

新用户除非是超级用户，否则无权创建新用户查询或查询类别。这旨在保护数据库中的信息。所需权限包括：

• 新查询：使用查询生成器创建新查询
• 创建/编辑类别：在查询管理器中创建和编辑类别
• 查询向导：使用查询工具
• 查询管理器：使用查询管理器管理保存的查询
• 报表计划：计划将保存的查询作为报表

注意：要在 SAP HANA 中查看仪表盘和交互式视图，必须为分析主题区域设置权限。

以下是本次课程需要掌握的要点。请花几分钟时间回顾以下要点：

• SQL 查询可用于生成特殊报表，作为设计更复杂报表的第一步，通过用户警报和审批程序，以及在实施项目期间验证导入到表中的迁移数据。
• 系统信息可帮助识别表和字段名称或物料编号和列编号。使用"视图">"系统信息"命令可以显示系统信息。
• 有两种工具可帮助您创建 SQL 查询 - 查询向导和查询生成器。尝试两种工具以查看您喜欢哪个。
• 您可以将查询保存为用户查询，并按类别对其进行组织。查询管理器允许您组织和管理已保存的用户查询。
• 用户需要权限才能运行已保存的用户查询。首先，为保存查询的类别选择查询权限组，然后在常规权限窗口中将用户分配到类别的查询权限组。
• 对于在活动窗口中处理单据时运行的查询，使用特殊语法。这主要适用于审批流程中使用的查询。