Skip to Content
0
Aug 02, 2023 at 08:55 PM

Using dats_add_months in Where Clause, it's possible?

343 Views

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

message.png

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

Attachments

message.png (1.6 kB)