topic Re: BETWEEN condition in Select Query Join in Application Development and Automation Discussions

BETWEEN condition in Select Query Join

Former Member — Sun, 21 Jan 2024 22:00:40 GMT

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

Re: BETWEEN condition in Select Query Join

umayaraj — Sat, 07 Jan 2017 06:58:32 GMT

hi Ilakkiaraj ,

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

thanks .

Re: BETWEEN condition in Select Query Join

Former Member — Sat, 07 Jan 2017 09:47:29 GMT

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.

Re: BETWEEN condition in Select Query Join

retired_member — Sat, 07 Jan 2017 10:38:05 GMT

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, ...

Re: BETWEEN condition in Select Query Join

Former Member — Mon, 09 Jan 2017 02:30:29 GMT

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