Skip to Content

BETWEEN condition in Select Query Join

Jan 07, 2017 at 03:51 AM


avatar image

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
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"...?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Horst Keller
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, ...

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks for link Mr. Keller. Now I understand why I was not able to do it the way I wanted to.

umayaraj B Jan 07, 2017 at 06:58 AM

hi Ilakkiaraj ,

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

thanks .

Show 1 Share
10 |10000 characters needed characters left 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.