topic Re: JOIN ON put a condition in Application Development and Automation Discussions

JOIN ON put a condition

ekekakos — Sat, 26 Jun 2021 09:49:59 GMT

Hello can someone tell me if the below code is possible to be written in a way?

Namely, to have a condition in the ON of a JOIN

    SELECT CASE WHEN @str_control_table-alter_ktopl = 'X' THEN skb1~altkt
                WHEN @str_control_table-alter_ktopl = ' ' THEN ska1~saknr
           END AS saknr,
           skat~txt50,ska1~xbilk,skb1~mitkz, skb1~mwskz
      FROM ska1 
        INNER JOIN skb1
          ON ska1~saknr = skb1~saknr
        LEFT OUTER JOIN skat
          ON ska1~ktopl = skat~ktopl AND
             skat~saknr = @( COND #( WHEN str_control_table-alter_ktopl = ' ' THEN skb1~saknr
                                     WHEN str_control_table-alter_ktopl = 'X' THEN skb1~altkt ) ) AND
             skat~spras = 'G'
      WHERE ska1~ktopl = @main_chart_of_account AND
            ska1~saknr IN @select_options_structure-s_saknr AND
            skb1~bukrs = @select_options_structure-p_bukrs AND
            skb1~xloeb <> 'X' AND
            skb1~xspeb <> 'X' AND
            skb1~altkt <> ''
      INTO TABLE @company_accounts.

In the above code I have the following error: Class "ZCL_EB_CHART_OF_ACCOUNTS_MDL" does not contain an interface "SKB1".

Thanks

Elias

Re: JOIN ON put a condition

Gourab_Dey — Sat, 26 Jun 2021 12:25:11 GMT

It's a good approach Elias, but I think it's not yet supported by ABAP SQL. Check out the below links with all the new enhancement to ABAP SQL.

New ABAP SQL Features

Unfortunately, you have to write 2 Select statements based on the condition.First, you have to select the data from skb1 . Then second select query with the text able sakt.

SELECT CASE WHEN @str_control_table-alter_ktopl = 'X' THEN skb1~altkt
             WHEN @str_control_table-alter_ktopl = ' ' THEN ska1~saknr
        END AS saknr,
        skat~txt50,ska1~xbilk,skb1~mitkz, skb1~mwskz
   FROM ska1
     INNER JOIN skb1
       ON ska1~saknr = skb1~saknr
   WHERE ska1~ktopl = @main_chart_of_account AND
         ska1~saknr IN @select_options_structure-s_saknr AND
         skb1~bukrs = @select_options_structure-p_bukrs AND
         skb1~xloeb <> 'X' AND
         skb1~xspeb <> 'X' AND
         skb1~altkt <> ''
   INTO TABLE @DATA(company_accounts).

IF company_accounts IS NOT INITIAL.

  SELECT saknr, skat~txt50
    FROM sakt
     FOR ALL ENTRIES IN company_accounts
   WHERE saknr = company_accounts-saknr
    AND  skat~spras = 'G'.

ENDIF.

Thanks,

Gourab

Re: JOIN ON put a condition

Sandra_Rossi — Sat, 26 Jun 2021 12:49:32 GMT

Inside a Host Expression (@( ... )), you can only use ABAP, not ABAP SQL syntax (i.e. table~column cannot appear inside it + you can't use any SQL function, etc.)

Because you need to use table~column, you must use ABAP SQL exclusively, one workaround is:

...
  AND ( ( @str_control_table-alter_ktopl = ' ' AND skat~saknr = skb1~saknr )
     OR ( @str_control_table-alter_ktopl = 'X' AND skat~saknr = skb1~altkt ) )
...

(if str_control_table-alter_ktopl equals ' ', the SQL statement sent to the database will be AND ( ( ' ' = ' ' AND skat~saknr = skb1~saknr ) OR ( ' ' = 'X' AND skat~saknr = skb1~altkt ) ))

Re: JOIN ON put a condition

ekekakos — Sat, 26 Jun 2021 16:07:39 GMT

Sandra thanks a lot. You also give me a solution to the above on , so the code is now

ON ( ( @str_control_table-alter_ktopl = ' ' AND ska1~ktopl = skat~ktopl ) OR
     ( @str_control_table-alter_ktopl = 'X' AND skat~ktopl = @str_account_plan_data-companyinfo-chartcode ) ) AND
   ( ( @str_control_table-alter_ktopl = ' ' AND skat~saknr = skb1~saknr ) OR
     ( @str_control_table-alter_ktopl = 'X' AND skat~saknr = skb1~altkt ) ) AND
   skat~spras = 'G'

The only thing that I do not know (and do not know if exists) is if there is no TXT50 with SPRAS = G then search with SPRAS = E.

For this reason I have the below LOOP after the SELECT

    LOOP AT company_accounts ASSIGNING FIELD-SYMBOL(<wa_company_accounts>) WHERE txt50 = ''.
      SELECT SINGLE txt50 FROM skat
      WHERE ktopl = @main_chart_of_account AND
            saknr = @<wa_company_accounts>-saknr AND
            spras = 'E'
      INTO @<wa_company_accounts>-txt50.
    ENDLOOP.

Sth that I do not like at all.

Again thanks

Re: JOIN ON put a condition

ekekakos — Sat, 26 Jun 2021 16:08:53 GMT

Gourab thanks but I think that sth is missing.

Re: JOIN ON put a condition

Sandra_Rossi — Sat, 26 Jun 2021 19:38:00 GMT

I agree, I don't like it at all, too. Instead, you could use a second LEFT OUTER JOIN skat on SPRAS = 'E' (so, you also need to use table aliases to differentiate the columns from the "two SKAT"), and in your ABAP code, you will take the 'G' or 'E' text column as you wish.

It makes a complex SELECT. If you want to simplify it by using skat~spras IN ('G','E'), add the condition AND skat~txt50 IS NOT NULL in the WHERE clause, but you may get duplicate lines if texts exist in both Greek and English languages that you will need to remove with for instance a SORT company_accounts BY saknr spras descending so that 'G' appears 'E', followed by a DELETE ADJACENT DUPLICATES FROM company_accounts COMPARING saknr.

(something like that)