在没有 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 orLOOP 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
) andFETCH
the results in packets. But that will degrade performance even further. - Never forget the
IF itab1 IS NOT INITIAL
check when usingFOR 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 theWHERE
conditions which mention theFOR ALL ENTRIES
table get ignored completely and you receive all the table rows withkoart = '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屋!