Using BETWEEN operator in a WHERE clause with dates from an internal table

Question

I have an internal table populated with start and end dates for each type of period. I want to use this internal table in a WHERE clause of an SQL query to select items whose start and end dates are within the open period of their respective type.

TYPES: BEGIN OF s_openprd,
  TETXT    TYPE TETXT,
  fromdate TYPE d,
  todate   TYPE d,
END OF s_openprd.

DATA: it_openprd TYPE TABLE OF s_openprd WITH KEY TETXT.

SELECT * FROM FPLT
INNER JOIN @it_openprd AS OP ON FPLT~TETXT = OP~TETXT
WHERE FPLT~FKDAT BETWEEN OP~fromdate AND OP~todate
AND   FPLT~NFDAT BETWEEN OP~fromdate AND OP~todate

However I get the error saying that OP~fromdate should be of a compatible type to be used as an operator with BETWEEN. The types listed include the date type d.

I've tried replacing BETWEEN with regular >= and <= operators:

SELECT * FROM FPLT
INNER JOIN @it_openprd AS OP ON FPLT~TETXT = OP~TETXT
WHERE FPLT~FKDAT >= OP~fromdate AND FPLT~FKDAT <= OP~todate
AND   FPLT~NFDAT >= OP~fromdate AND FPLT~NFDAT <= OP~todate

But the query returns incorrect results.

I assume the ABAP type d is incompatible with SQL type d ? How can I use an internal table to restrict the selection in this way ?

I can't replicate the issue. It's compiling fine for me, can't test since no data. Are you getting design time or runtime error? May be provide some more details on code along with explanation for easier understanding. Thanks — Swadhin, Sep 02 '21 at 01:54
Please tell us the types of the columns `FKDAT` and `NFDAT` in table `FPLT`, as it seems to be a critical information to answer your question. — Sandra Rossi, Sep 02 '21 at 05:55
In table `FPLT`, `FKDAT` and `NFDAT` have the domain DATUM, type DATS of length 8. — Cutter, Sep 02 '21 at 07:48
@Swadhin this is a design time error. The exact error message is: "Comparisons using BETWEEN in this position are possible only if FROMDATE has a valid type. This type can be I, P, 8, F, C, D, or T." — Cutter, Sep 02 '21 at 08:01
@Cutter Yup. Got the error in 7.54 but not in 7.57 ... unless you get any other suggestion, would suggest you to check with SAP. — Swadhin, Sep 02 '21 at 08:51
@Swadhin thanks for checking it. I'm stuck with the older version unfortunately. ANy other way to do what I'm trying to do without relying on BETWEEN? The problem with `FPLT-NFDAT` and `FPLT-FKDAT` is that their order is not consistent. In one entry, the value of `NFDAT` may be anterior to `FKDAT` and in another entry it's the opposite. So it's tricky to make comparisons with `>=` `<=` operators. — Cutter, Sep 02 '21 at 09:05
In fact, you have found the workaround (using >= and <= instead of BETWEEN) but you incorrectly think that it returns "incorrect results". So your question is not about BETWEEN, it's about giving details about the input data, what you currently get and what you expect. — Sandra Rossi, Sep 02 '21 at 11:27

Suncatcher · Answer 1 · 2021-11-19T14:14:54.433

Nothing prevents you from using FOR ALL ENTRIES instead of joining with internal table, if you ABAP version does not support it.

Regarding "incorrect results" I agree with Sandra, BETWEEN and LT/GT have totally identical sense, so it is more a matter of what you expect than correctness. I'd rather utilize standard logic for dealing with the issue that bothers you:

The problem with FPLT-NFDAT and FPLT-FKDAT is that their order is not consistent. In one entry, the value of NFDAT may be anterior to FKDAT and in another entry it's the opposite.

Following the same approach for you SQL query, you can write something like this:

TYPES: BEGIN OF ty_fplt, 
         fplnr  TYPE fplnr, 
         fkdat  TYPE fkdat, 
         nfdat  TYPE nfdat, 
       END OF ty_fplt, 
       tt_fplt TYPE STANDARD TABLE OF ty_fplt WITH NON-UNIQUE KEY fkdat nfdat. 
 
DATA(lt_fplt_base) = VALUE tt_fplt( ). 
 
SELECT fplnr, CASE WHEN nfdat < fkdat THEN nfdat ELSE fkdat END AS fkdat, 
              CASE WHEN nfdat < fkdat THEN fkdat ELSE nfdat END AS nfdat 
  FROM fplt 
  INTO TABLE @lt_fplt_base. 
 
SELECT * 
  FROM fplt AS f 
  INTO TABLE @DATA(result) 
  FOR ALL ENTRIES IN @lt_fplt_base 
  WHERE f~fplnr = @lt_fplt_base-fplnr 
    AND f~fkdat >= @lt_fplt_base-fkdat 
    AND f~nfdat <= @lt_fplt_base-nfdat.

Don't take it as a rule of thumb, it is just a quick suggestion.

P.S. Joining by text field INNER JOIN @it_openprd AS OP ON FPLT~TETXT = OP~TETXT does not make sense in any context. Text/string fields are often ambiguous, they often contain control characters, whitespaces, etc., which make them useless for primary key.

Using BETWEEN operator in a WHERE clause with dates from an internal table

1 Answers1