英文:
ABAP SQL statement with WHERE BETWEEN clause problem
问题
我需要提取在日期范围内的数据。当我通过 SE16N 手动操作时，我得到了正确的答案（1000条记录）。当我通过 SELECT 查询进行操作时，我只得到6条记录。这个查询有问题吗？
DATA: lv_document_date_since TYPE d,
lv_document_date_until TYPE d.
SELECT bk~belnr
FROM bset AS bt INNER JOIN bkpf AS bk ON ( bt~bukrs = bk~bukrs AND
bt~belnr = bk~belnr AND
bt~gjahr = bk~gjahr )
WHERE
bk~bldat BETWEEN @lv_document_date_since
AND @lv_document_date_until AND
bt~bukrs = @mv_bukrs AND
bt~mwskz IN @mt_mwskz AND
bk~budat IN @mt_budat
ORDER BY bk~belnr
INTO CORRESPONDING FIELDS OF TABLE @me->my_data.
英文:
I need to extract the data from between the range of dates. When I do it manually through SE16N I get correct answer (1000 records). When I do it through SELECT query, I get only 6 records. Is there any problem within this query?
DATA: lv_document_date_since TYPE d,
lv_document_date_until TYPE d.
SELECT bk~belnr
FROM bset AS bt INNER JOIN bkpf AS bk ON ( bt~bukrs = bk~bukrs AND
bt~belnr = bk~belnr AND
bt~gjahr = bk~gjahr )
WHERE
bk~bldat BETWEEN @lv_document_date_since
AND @lv_document_date_until AND
bt~bukrs = @mv_bukrs AND
bt~mwskz IN @mt_mwskz AND
bk~budat IN @mt_budat
ORDER BY bk~belnr
INTO CORRESPONDING FIELDS OF TABLE @me->my_data.
答案1
得分: 1
你不能在 bset 表上使用 JOIN，因为它是一个 簇表，不允许在簇/池表和投影视图上进行JOIN操作。
在这种情况下，可以使用 FOR ALL ENTRIES 将单个SELECT语句拆分为两个SELECT，像这样：
SELECT bukrs, belnr, gjahr FROM bkpf INTO TABLE @DATA(lt_bkpf)
WHERE bldat BETWEEN @lv_document_date_since AND @lv_document_date_until AND
bukrs = @mv_bukrs AND
budat IN @mt_budat.
IF lines( lt_bkpf ) > 0.
SELECT belnr FROM bset INTO TABLE @DATA(lt_bset)
FOR ALL ENTRIES IN @lt_bkpf
WHERE bukrs = @lt_bkpf-bukrs AND
belnr = @lt_bkpf-belnr AND
gjahr = @lt_bkpf-gjahr AND
mwskz IN @mt_mwskz.
SORT lt_bset BY belnr.
ENDIF.
使用日期范围的选择应该有效。尝试在调试器中检查条件变量中的值以及返回的内容。
英文:
You cannot make SELECT with JOIN on bset table, because it is a cluster table, and JOINs are not permitted for cluster / pooled tables and projection views.
In this case it is possible to split the single select statement into two selects using FOR ALL ENTRIES like this:
SELECT bukrs, belnr, gjahr FROM bkpf INTO TABLE @DATA(lt_bkpf)
WHERE bldat BETWEEN @lv_document_date_since AND @lv_document_date_until AND
bukrs = @mv_bukrs AND
budat IN @mt_budat.
IF lines( lt_bkpf ) > 0.
SELECT belnr FROM bset INTO TABLE @DATA(lt_bset)
FOR ALL ENTRIES IN @lt_bkpf
WHERE bukrs = @lt_bkpf-bukrs AND
belnr = @lt_bkpf-belnr AND
gjahr = @lt_bkpf-gjahr AND
mwskz IN @mt_mwskz.
SORT lt_bset BY belnr.
ENDIF.
The selection using between dates should work. Try to check in debugger which values are inside condition variables and what is returned.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论