Using dats_add_months in Where Clause, it's possible?

[Oscar_Alberto]

Greetings to all.

For reference I have a BW background and some basic ABAP knowledge (I'm not an expert) and I'm working on BW/4HANA 2 SP 11 with Eclipse, BWMT and ADT.

Now, in BW I have a transformation whose runtime is ABAP and it has an end routine.

You probably already know, but if you don't I clarify that, speaking of information management in BW, the recommendation is to manipulate the information in memory trying to consult the entire data package that is being processed to speed up the process.

That said, what I want to achieve is that based on a given date, for example 12/31/2023, consult the records of last year, this is to consult the info of 12/31/2022

The /BIC/AZXXXX7 object has the information from last year and the package (internal table) RESULT_PACKAGE contains the pivot date on which the data search should be performed but from last year.

For the above I am using a FOR ALL ENTRIES IN along with the DATS_ADD_MONTHS function in the where clause on the pivot field that is in the RESULT_PACKAGE, the line with the error is WHERE calday = dats_add_months(result_package-calday, -12). Needless to say, the code gives me an error.

I show you the code

METHOD GLOBAL_END.

     FIELD-SYMBOLS:
       <RESULT_FIELDS>    TYPE _ty_s_TG_1.

  DATA: MONITOR_REC     TYPE rstmonitor.

* Note the _M class are not considered for DTP execution.
* ABAP Breakpoints must be set in the generated program instead

* ABAP Breakpoints within an ABAP End-Routine must be set
* in the _A class if the transformation runtime is set to SAP HANA!

**** begin of routine - insert your code only below this line       ****

... "insert your code here

    DATA: it_posa TYPE STANDARD TABLE OF /bic/azavtapost7.
    DATA: wa_posa LIKE LINE OF it_posa.

*   Consulta el cubo totalizado
    SELECT *
    FROM /bic/azavtapost7
    INTO TABLE it_posa
    FOR ALL ENTRIES IN result_package
    WHERE calday = dats_add_months(result_package-calday, -12)
      AND plant =  result_package-plant
      AND material = result_package-material.
    SORT it_posa BY calday plant material.

**** end of routine - insert your code only before this line        ****
ENDMETHOD.

But it shows me the following error

Is this possible or am I completely wrong, what possible solution exists, I would not want to do it within a Loop - EndLoop.BR Oscar

[Sandra_Rossi]

Usual error when you're a newbie in ABAP. You are missing the spaces between the words.

ABAP Keyword Documentation (sap.com) example:

SELECT SINGLE dats1, datn2, utcl1,
              extract_year( dats1 ) AS extract_year,
              extract_month( datn2 ) AS extract_month,
              extract_day( utcl1 ) AS extract_day,
              dayname( dats1 ) AS dayname,
              monthname( datn1 ) AS monthname,
              weekday( utcl1 ) AS weekday,
              days_between( dats1,utcl1 ) AS days_between,
              add_days( datn2,2 ) AS add_days,
              add_months( utcl1,4 ) AS add_months
       FROM demo_expressions
       INTO @FINAL(result).

[Oscar_Alberto]

Greetings to all.

sandra.rossi, thanks for your answer.

Following your advice and reviewing the documentation, I made the corresponding changes but the error persists. Next I copy the final code already with the correction.

Below I copy the final code with the correction and the error message that continues to be generated

SELECT *
FROM /bic/azavtapost7
INTO TABLE it_posa
FOR ALL ENTRIES IN result_package
WHERE calday = dats_add_months( result_package-calday,12 )
  AND plant =  result_package-plant
  AND material = result_package-material.
SORT it_posa BY calday plant material.

Now, I did some tests directly on SE38 with an ABAP report. The first example worked perfectly using that function in the SELECT statement.

SELECT plant, material, dats_add_months( calday,-12 ) AS test
FROM /bic/azavtapost7
WHERE plant =  'T013'
	AND calday = '20230610'
INTO TABLE @it_posb.

The second attempt was using the same line of code but in the WHERE statement, in this case a syntax error is generated.

SELECT plant, material, calday
FROM /bic/azavtapost7
WHERE plant =  'T013'
	AND calday = '20230610'
	AND calday = dats_add_months( calday,-12 )
INTO TABLE @it_posb.<br>

With the above, my guess is that the DATS_ADD_MONTH function is not supported in the WHERE statement. Could Sandra Rossi or some other ABAP expert if I am correct?

BR Oscar.

[Sandra_Rossi]

Before the version 7.55 (ABAP SQL - 10. SQL conditions revised), after WHERE and HAVING (doesn't concern CASE and path expressions), the SQL functions must be on the left side.

WHERE dats_add_months( result_package-calday,12 ) = calday

[Sandra_Rossi · Accepted solution]

Two errors

1) Usual error when you're a newbie in ABAP. You are missing the spaces between the words.

ABAP Keyword Documentation (sap.com) example:

SELECT SINGLE dats1, dats2,
              dats_is_valid( dats1 ) AS valid,
              dats_days_between( dats1, dats2 ) AS days_between,
              dats_add_days( dats1,100 ) AS add_days,
              dats_add_months( dats1,-1 ) AS add_month,                " <================
              datn1, datn2,
              datn_days_between( datn1, datn2 ) AS datn_between,
              datn_add_days( datn1,1 ) AS datn_add_days,
              datn_add_months( datn1,1 ) AS datn_add_month
       FROM demo_expressions
       INTO @FINAL(result).

2) Error or NOT, depends on version

Before the version 7.55 (ABAP SQL - 10. SQL conditions revised), after WHERE and HAVING (doesn't concern CASE and path expressions), the SQL functions must be on the left side.

WHERE dats_add_months( result_package-calday,12 ) = calday<br>

[Oscar_Alberto · Accepted solution]

Thank you sandra.rossi.