在没有 INNER JOIN 的选择中使用表 BSEG [英] Using the table BSEG in a selection without INNER JOIN

问题描述

我正在尝试使用以下代码从表 BSEG 中选择字段 KOART:

I am trying to do a selection of the field KOART from the table BSEG, by using the below code:

CONSTANTS: lc_d TYPE c VALUE 'D'.
    SELECT k~bukrs,
         k~belnr,
         k~budat,
         k~gjahr,
         k~buzei,
         k~kunnr,
         i~banks,
         i~bankl,
         i~bankn
    FROM bsid AS k
    INNER JOIN knbk AS i ON k~kunnr EQ i~kunnr
*    INNER JOIN bseg
    INTO TABLE @DATA(lt_data_knbk)
    WHERE k~bukrs IN @so_bukrs
    AND k~budat IN @so_budat
    AND k~blart IN @so_blart
*    AND bseg~koart = @lc_d.

通常我会为表添加内连接语法，但由于 bseg 是一个簇表，它不接受内连接选项，因此我不能使用字段 koart.

Normally I would have put the Inner join syntax for the table, but being that bseg is a cluster table, it does not accept the inner join option, and thus I cannot use the field koart.

有谁知道，如何将BSEG的字段KOART放在上面的选择中?

May anyone know, how can I put the field KOART of the BSEG in the selection above?

非常感谢您的帮助！

推荐答案

如 József Szikszai 所述，内连接在这个特定用例中实际上并不是必需的，因为表 BSID 无论如何都只包含客户文档.

As explained by József Szikszai, an inner join is not actually necessary in this particular use-case, because the table BSID contains only customer documents anyway.

但如果在这里是必要的，那么您有两个选择:

But if it were necessary here, then you would have two options:

首先是切换到 SAP HANA 作为数据库后端，因为这样所有集群表都将转换为常规表.由于 SAP 宣布他们将从 2027 年开始为支持其他数据库收取额外费用，并在 2030 年完全放弃对非 HANA 的支持，因此从长远来看，您将无法解决这个问题.但由于 HANA 转换所涉及的成本和规划工作，如果您今天面临这个问题，这可能不是一个严肃的选择.

The first would be to switch to SAP HANA as database backend, because then all the cluster tables will be converted to regular tables. You won't get around this in the long-term due to SAP announcing that they will be charging extra for supporting other databases from 2027 and drop support for non-HANA in 2030 completely. But due to the cost and planning effort involved in a HANA conversion, this is likely not a serious option if you are facing this problem today.

当您在不可避免的 HANA 转换之前需要临时解决方案时，还有第二种选择.您可以使用带有 FOR ALL ENTRIES 条件的第二个选择来解决此问题.

When you need a temporary solution until your inevitable HANA conversion, then there is a second option. You can solve this problem using a second select with a FOR ALL ENTRIES condition.

SELECT bukrs,
     belnr,
     budat,
     gjahr,
     buzei,
     kunnr,
  FROM bsid     
  INTO TABLE @DATA(lt_data_bsid)
  WHERE bukrs IN @so_bukrs
  AND budat IN @so_budat
  AND blart IN @so_blart.

IF lt_data_bsid IS NOT INITIAL.
  SELECT bukrs,
         belnr,
         gjahr,
         buzei
    FROM bseg
    INTO TABLE @DATA(lt_bseg)
    FOR ALL ENTRIES IN @lt_data_bsid
    WHERE bukrs = @lt_data_bsid-bukrs
      AND belnr = @lt_data_bsid-belnr
      AND gjahr = @lt_data_bsid-gjahr
      AND buzei = @lt_data_bsid-buzei
      AND koart = @lc_d.
         
ENDIF.

请注意，这种方法有很多问题:

Note that this approach has a number of problems:

• 您现在有两个内部表而不是一个内部表中的数据，因此您可能需要执行几个READ TABLE 或 LOOP AT... WHERE's 将数据拼凑在一起.
• 两个 SELECT 而不是一个意味着到数据库服务器的两次网络往返而不是一次.您还错过了您的数据库可能能够为 JOIN 执行的大多数内部优化.这会增加程序的运行时间.
• 第一个 SELECT 可能会返回大量您不需要的数据，因此您可能会遇到内存问题.您可以通过使用数据库游标 (OPEN CURSOR) 和 FETCH 数据包中的结果来解决该问题.但这会进一步降低性能.
• 在使用 FOR ALL ENTRIES 时，永远不要忘记 IF itab1 IS NOT INITIAL 检查.直观地，如果查找表为空，人们会假设根本不接收任何数据库行.但实际上会发生的是，提到 FOR ALL ENTRIES 表的 WHERE 条件被完全忽略，并且您会收到 所有 带有 <代码>koart = 'D'.

• You now have the data in two internal tables instead of one, so you might have to perform a couple READ TABLE's or LOOP AT... WHERE's to puzzle the data together.
• Two SELECTs instead of one mean two network roundtrips to the database server instead of one. You also miss out on most internal optimizations your database might be able to do for JOINs. Which increases the runtime of your program.
• The first SELECT might return a ton of data you don't need, so you might run into memory problems. You can solve that by using a database cursor (OPEN CURSOR) and FETCH the results in packets. But that will degrade performance even further.
• Never forget the IF itab1 IS NOT INITIAL check when using FOR ALL ENTRIES. Intuitively, one would assume to receive no database rows at all if the lookup table is empty. But what actually will happen is that the WHERE conditions which mention the FOR ALL ENTRIES table get ignored completely and you receive all the table rows with koart = 'D'.

但是，如果您必须对集群表进行 JOIN，那么很遗憾没有其他选择.

But if you have to do a JOIN with a cluster-table, then there is unfortunately no other option.

这篇关于在没有 INNER JOIN 的选择中使用表 BSEG的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！