About ABAP SQL join expressions

[Former Member]

Dear all,

There is an issue that I've written a SQL (5 Tables Joined ), But now the join condition has been changed.

So can the ABAP SQL solve the problem.

Here is the code . My requirement is

INNER JOIN PA0008 AS B
ON B~PERNR = A~PERNR

AND B~BEGDA <= A~BEGDA - 1 ← this is requirement point
AND B~ENDDA >= A~BEGDA - 1 ← this is requirement point

 SELECT A~PERNR, 
        A~ENDDA, 
        A~BEGDA, 
        A~MASSN, 
        A~MASSG, 
        B~TRFGR, 
        B~TRFST, 
        C~PERSG, 
        C~ANSVH, 
        D~PTEXT, 
        E~ATX 
   FROM PA0302 AS A
  INNER JOIN PA0008 AS B
     ON B~PERNR = A~PERNR
    AND B~BEGDA <= A~BEGDA
    AND B~ENDDA >= A~BEGDA
  INNER JOIN PA0001 AS C
     ON C~PERNR = A~PERNR
    AND C~BEGDA <= A~BEGDA
    AND C~ENDDA >= A~BEGDA
   LEFT OUTER JOIN T501T AS D 
     ON D~PERSG = C~PERSG
    AND D~SPRSL = @SY-LANGU
   LEFT OUTER JOIN T542T AS E 
     ON E~ANSVH = C~ANSVH
    AND E~SPRAS = D~SPRSL
    AND E~MOLGA = @CNS_COM-MOLGAJ
   INTO TABLE @O_TB_COMMON
    FOR ALL ENTRIES IN @I_TB_PERNR
  WHERE A~PERNR = @I_TB_PERNR-PERNR
    AND A~BEGDA >= @S_PERIOD-LOW
    AND A~BEGDA <= @S_PERIOD-HIGH
    AND (
          ( A~MASSN = @CNS_MSN-MASSN_20
        AND A~MASSG = @CNS_ARY-MASSG_20_01 )
         OR A~MASSN = @CNS_MSN-MASSN_70
         )
    AND B~TRFGR IN ( @CNS_TRFGR-MNG, 
        @CNS_TRFGR-KA, 
        @CNS_TRFGR-KB, 
        @CNS_TRFGR-KC, 
        @CNS_TRFGR-JA,
        @CNS_TRFGR-JB, 
        @CNS_TRFGR-JC, 
        @CNS_TRFGR-I1, 
        @CNS_TRFGR-I2, 
        @CNS_TRFGR-I3, 
        @CNS_TRFGR-IS ). 

[Tomas_Buryanek]

I can not test it since it is only from 7.50 and up. There are arithmetic expressions:

https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/abensql_arith.htm

But there is no DATE listed in documentation, so it might not work for date field...

[Former Member]

So, as you said.

It seems that it really doesn't work.

I decided that split it to five SQL .So sad................ *^*

But thank you all the same.

[Tomas_Buryanek]

liuyiping Yeah, I am afraid split SQL will be needed. You can try explore CDS if you are familiar with it or native SQL. But that is probably all can be done...

[Sandra_Rossi]

In fact, there are date functions, but it's difficult to find them in the ABAP documentation:

* sql_elem (supported elementary types)
* sql_func
*        SQL functions
*               Numeric functions (ABS, DIV, MOD, ...)
*               String functions (CONCAT, REPLACE, ...)
*               Coalesce function (COALESCE)
*        Special functions
*               Conversion functions (BINTOHEX, HEXTOBIN)
*               Date functions and time functions (DATS_IS_VALID, DATS_ADD_DAYS, ...)
* sql_num (arithmetic operators + - * /)
* sql_cast (CAST)
* sql_string (&&)
* sql_case (CASE ... WHEN ... [ELSE ...] END)

cf my answer with DATS_ADD_DAYS.

[balbino_soaresferreirafil]

You can't do it this way using open SQL. You will need to split the query in two. In the first, select data from PA0008 with BEGDA - 1 and put in a internal table, and then, use the for all entries clause to join this table with the second.

[Former Member]

YES！
Maybe only this way can solve it. Thanks brother.

[Sandra_Rossi]

Isn't B~BEGDA <= A~BEGDA - 1 the same as B~BEGDA < A~BEGDA ?

[Former Member]

I think B~BEGDA <= A~BEGDA - 1 the same as B~BEGDA < A~BEGDA - 2.
Thank you.

[Sandra_Rossi]

hmmm let's say A~BEGDA = 10

Then B~BEGDA <= 9 is the same as B~BEGDA < 10

Isn't it?

[Former Member]

YES!
Sorry for the last wrong words.

You're right.

B~BEGDA <= A~BEGDA - 1 the same as B~BEGDA < A~BEGDA

This is OK.

But the another condition B~ENDDA >= A~BEGDA - 1

I'm afraid that its not the same as B~ENDDA > A~BEGDA.

So... You know , I have to split the SQLs...

[DoanManhQuynh]

一平 刘:i think you can switch the position of B~ENDDA >= A~BEGDA - 1 to A~BEGDA < B~ENDDA...give it a try.

[Sandra_Rossi]

一平 刘 sorry I hadn't look at the second expression, you're right, it won't work.

[Sandra_Rossi]

EDIT: whole answer changed.

EDIT: this answer is valid for ABAP >= 7.51

To operate on dates, you have the date functions, especially DATS_ADD_DAYS to add or subtract days.

As No SQL expressions can be used on the right side of relational expression, you need to switch the order of the operands in your case.

To simplify, let's state that your starting SQL is :

 SELECT *
   FROM PA0302 AS A
  INNER JOIN PA0008 AS B
     ON B~PERNR = A~PERNR
    AND B~BEGDA <= A~BEGDA
    AND B~ENDDA >= A~BEGDA
   INTO TABLE @DATA(O_TB_COMMON).

Then you change it to:

 SELECT *
   FROM PA0302 AS A
  INNER JOIN PA0008 AS B
     ON B~PERNR = A~PERNR
    AND DATS_ADD_DAYS( A~BEGDA, -1 ) >= B~BEGDA
    AND DATS_ADD_DAYS( A~BEGDA, -1 ) <= B~ENDDA
  INTO TABLE @DATA(O_TB_COMMON).

[Former Member]

Thanks for you kindly answer very much.

But I'm afraid that my ABAP release version is 740, so I can't use the new ABAP SQL.

Is that right? If the answer is 'Yes'.

I want to Accept your answer and share to others to learn it.

Thank you again.

[Sandra_Rossi]

一平 刘 Oh, right, it's only ABAP >= 7.51