ABAP SQL statement with WHERE BETWEEN clause problem

Question

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.

Answer 1

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.