topic Re: About ABAP SQL join expressions in Application Development and Automation Discussions

About ABAP SQL join expressions

Former Member — Mon, 18 Mar 2019 10:28:00 GMT

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

Re: About ABAP SQL join expressions

Tomas_Buryanek — Tue, 19 Mar 2019 07:03:59 GMT

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

Re: About ABAP SQL join expressions

Former Member — Tue, 19 Mar 2019 08:00:14 GMT

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.

Re: About ABAP SQL join expressions

Tomas_Buryanek — Tue, 19 Mar 2019 08:49:00 GMT

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

Re: About ABAP SQL join expressions

balbino_soaresferreirafil — Tue, 19 Mar 2019 12:01:33 GMT

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.

Re: About ABAP SQL join expressions

Former Member — Tue, 19 Mar 2019 13:11:53 GMT

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

Re: About ABAP SQL join expressions

Sandra_Rossi — Tue, 19 Mar 2019 13:27:03 GMT

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

Re: About ABAP SQL join expressions

Former Member — Wed, 20 Mar 2019 05:47:09 GMT

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

Re: About ABAP SQL join expressions

Sandra_Rossi — Wed, 20 Mar 2019 05:52:00 GMT

hmmm let's say A~BEGDA = 10

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

Isn't it?

Re: About ABAP SQL join expressions

Former Member — Thu, 21 Mar 2019 04:02:01 GMT

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

Re: About ABAP SQL join expressions

DoanManhQuynh — Thu, 21 Mar 2019 04:49:44 GMT

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

Re: About ABAP SQL join expressions

Sandra_Rossi — Thu, 21 Mar 2019 06:33:33 GMT

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

Re: About ABAP SQL join expressions

Sandra_Rossi — Thu, 21 Mar 2019 08:04:44 GMT

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

Re: About ABAP SQL join expressions

Sandra_Rossi — Thu, 21 Mar 2019 12:36:52 GMT

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.

Re: About ABAP SQL join expressions

Former Member — Fri, 22 Mar 2019 03:59:49 GMT

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.

Re: About ABAP SQL join expressions

Sandra_Rossi — Fri, 22 Mar 2019 13:50:57 GMT

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