Skip to Content

BETWEEN condition in Select Query Join

I have Validity Date (DATAB) & Created Date (ERDAT) in my ZTable. I want to select records from EKBE where the BUDAT field is in between DATAB & ERDAT. I wanted to get it done in a single select query. I got my result from the following code. but it shows syntax error.

SELECT a~bukrs, a~werks, a~sl_no, a~ebeln, a~ebelp, a~matnr, a~bdatj, a~poper, a~bsart, a~lifnr, a~kschl, a~ernam, a~erdat, a~datbi, a~datab, a~meins, a~mwskz, a~kbetr_cr, a~kbetr_pr, b~belnr, b~budat,c~knumv, d~menge
FROM zco_poamd AS a
INNERJOIN ekbe AS b
ON  a~werks = b~werks
AND a~matnr = b~matnr
AND a~ebeln = b~ebeln
AND a~ebelp = b~ebelp
AND a~bdatj = b~gjahr
AND b~vgabe ='1'AND b~bwart IN('101','102','107','108','122','161','162','543','922')
WHERE b~budat >= a~datab
  AND b~budat <= a~erdat.

But my doubt is, why can't I get it done by the following code?

...WHERE b~budat BETWEEN a~datab AND a~erdat.

Like Mr. Horst Keller suggested, I understood that only host variables can be declared in the interval for the BETWEEN keyword. But as SAP HELP says, if BETWEEN keyword behaves similar to "b~budat >= a~datab AND b~budat <= a~erdat", why doesn't the keyword work like that and instead gives the syntax error "Incorrect expression "BETWEEN A~DATAB" in logical condition"...?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 07, 2017 at 10:38 AM

    BETWEEN is stricter than simple comparisons. Only numeric columns can be compared.

    It is under discussion to remove the restriction at least for NUMC, DATS, TIMS, ...

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 07, 2017 at 06:58 AM
    -1

    hi Ilakkiaraj ,

    Try selection option ,i think it's possible to full fill your requirement .

    thanks .

    Add comment
    10|10000 characters needed characters exceeded

    • My in between value range must be taken from the base table of the Select query, not through Select-options. I completed my requirement, I just want to understand the architecture of the BETWEEN keyword in SELECT statement.