topic Re: Right outer join alternative ? in Application Development and Automation Discussions

Right outer join alternative ?

Former Member — Tue, 17 Jul 2012 08:22:32 GMT

Hi,

I am a newbie in ABAP infact to SQL and completely new to sdn, this is my first post (question) .

I am facing an issue as right outer join is not supported in ABAP (as per my understanding). I am having a three tables such that one is grand parent, second is parent and last is child. So just to elaborate further, grand parent table is having one primary key, parent table is having a composite primary key which is derived from a primary key of the grand parent table and the primary key of the parent table and similarly child table has composite primary key of three fields where first field is primary key of grand parent table, second field is primary key of parent table and third field uniquely identify record in child table.

With this kind of table structure i can have an extra record in parent table for which no record exist in child table. Now i want to join all my three tables such that i get all the records in tables and if record doesn't exist in any child tables then those field should have null or blank values.

For this i tried left outer join but the problem i am facing is that tables which are parent in nature can only have extra record and hence i have to keep them on left side of outer join so that extra record is retrieved. But at the same time parent tables has less number of primary keys, so if i keep the parent table on left of outer join i cannot compare all the primary key of child table with the fields of parent table.

Hope you are able to understand the problem here. Following is the code i am using. This is returning more number of records as table LMCNTV and LMCNT_INS is also associated with one more field cvnr. And if i change the ordering then all extra record in parent tables are not fetched.

   DATA: BEGIN OF WA_CNT,
        CONT TYPE LMCNT,
        VERS TYPE LMCNTV,
        INS TYPE LMCNT_INS,
      END OF WA_CNT.

SELECT * INTO WA_CNT FROM LMCNT AS cnt LEFT OUTER JOIN LMCNTV AS cntv ON
                        cnt~MANDT = cntv~MANDT AND
                        cnt~CNR   = cntv~CNR

                       LEFT OUTER JOIN LMCNT_INS AS ins ON
                        cnt~MANDT = ins~MANDT AND
                        cnt~cnr   = ins~cnr.

ENDSELECT.

Ordering changed :

   SELECT * INTO WA FROM ( LMCNTV AS cntv LEFT OUTER JOIN LMCNT AS cnt ON
                        cnt~MANDT = cntv~MANDT AND
                        cnt~CNR   = cntv~CNR )

                       LEFT OUTER JOIN LMCNT_INS AS ins ON
                        cntv~MANDT = ins~MANDT AND
                        cntv~cnr   = ins~cnr AND
                        cntv~cvnr = ins~cvnr.

WRITE: / wa-cont-mandt, wa-cont-cnr, wa-vers-cvnr, wa-ins-ins_key.

ENDSELECT.

Please let me know if you require any further details.

Thanks for your help in advance,

Best Regards,

Lokesh

Re: Right outer join alternative ?

former_member186491 — Tue, 17 Jul 2012 13:40:42 GMT

Hi Lokesh,

Use INNER JOIN instead, this way you can achiev that same.

Thanks.

Kumar Saurav.

Re: Right outer join alternative ?

Former Member — Tue, 17 Jul 2012 13:55:01 GMT

Hi Lokesh

As you require the lines that are not matched in the child also to be displayed an inner join will not work. Here is a Referral to the Standard SAP help docs"

Resulting set for inner join

The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.

Resulting set for outer join

The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the resulting set for every selected line on the left-hand side, even if no line on the right-hand side fulfils the join_cond condition. The columns on the right-hand side that do not fulfil the join_cond condition are filled with null values.

Example

Join the columns carrname, connid, fldate of the database tables scarr, spfli and sflight by means of two inner joins. A list is created of the flights from p_cityfr to p_cityto. Alternative names are used for every table.

PARAMETERS: p_cityfr TYPE spfli-cityfrom,
            p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,
         fldate TYPE sflight-fldate,
         carrname TYPE scarr-carrname,
         connid   TYPE spfli-connid,
       END OF wa.

DATA itab LIKE SORTED TABLE OF wa
               WITH UNIQUE KEY fldate carrname connid.

SELECT c~carrname p~connid f~fldate
       INTO CORRESPONDING FIELDS OF TABLE itab
       FROM ( ( scarr AS c
         INNER JOIN spfli AS p ON p~carrid   = c~carrid
                              AND p~cityfrom = p_cityfr
                              AND p~cityto   = p_cityto )
         INNER JOIN sflight AS f ON f~carrid = p~carrid
                                AND f~connid = p~connid ).

LOOP AT itab INTO wa.
WRITE: / wa-fldate, wa-carrname, wa-connid.
ENDLOOP.
Example

Join the columns carrid, carrname and connid of the database tables scarr and spfli using an outer join. The column connid is set to the null value for all flights that do not fly from p_cityfr. This null value is then converted to the appropriate initial value when it is transferred to the assigned data object. The LOOP returns all airlines that do not fly from p_cityfr.

PARAMETERS p_cityfr TYPE spfli-cityfrom.

DATA: BEGIN OF wa,
        carrid   TYPE scarr-carrid,
        carrname TYPE scarr-carrname,
        connid   TYPE spfli-connid,
      END OF wa,
      itab LIKE SORTED TABLE OF wa
                WITH NON-UNIQUE KEY carrid.

SELECT s~carrid s~carrname p~connid
       INTO CORRESPONDING FIELDS OF TABLE itab
       FROM scarr AS s
       LEFT OUTER JOIN spfli AS p ON s~carrid   = p~carrid
                                  AND p~cityfrom = p_cityfr.

LOOP AT itab INTO wa.
IF wa-connid = '0000'.
    WRITE: / wa-carrid, wa-carrname.
ENDIF.
ENDLOOP.

Hope this gives you a bit more insight into your problem.

Regards

Vic

Re: Right outer join alternative ?

former_member129652 — Wed, 18 Jul 2012 02:31:09 GMT

Dear Lokesh Agrawal,

I studied your question. I guess that what you want to do is a chained left outer join (LMCNT left outer join LMCNTV left outer join LMCNT_INS). More specifically, you wan to do

SELECT *

FROM LMCNT AS cnt

LEFT OUTER JOIN LMCNTV AS cntv ON cnt~CNR = cntv~CNR

LEFT OUTER JOIN LMCNT_INS AS ins ON cntv~cnr = ins~cnr

AND cntv~cnvr = ins~cnvr.

However, a table can participate only one left outer join in SAP Open SQL. The ANSI SQL-92 allows chained left outer joins, but SAP open SQL doesn't.

You can divide it into separate steps.

Select from LMCNT left outer join LMCNTV.
Save the result into a temporary table.
Select from your temporary table left outer join LMCNT_INS.

There are more discussions in the following threads

Re: Right outer join alternative ?

Former Member — Wed, 18 Jul 2012 05:16:15 GMT

Hi Lee,

Thanks for your response, i have already tried saving the result of first left outer join to a temporary internal table (IT_CNT) and then tried applying left outer join on this internal temporary table. But unfortunately in this case ABAP compiler complains that

"IT_CNT" is not defined in the ABAP Dictionary as a table, projection view or Database view.

Following is what i tried:

   DATA: BEGIN OF wa_cnt,
        cnt TYPE LMCNT,
        vers TYPE LMCNTV,
      END OF wa_cnt.

   DATA: BEGIN OF it_cnt OCCURS 50,
        cnt TYPE LMCNT,
        vers TYPE LMCNTV,
      END OF it_cnt.

    DATA: BEGIN OF wa_cntv,
        cntv LIKE wa_cnt,
        ins TYPE LMCNT_INS,
      END OF wa_cntv.

      WRITE: / 'MANDT' , 'CNR', 'CVNR', 'NAME', 'DESC' .

      SELECT * INTO wa_cnt FROM LMCNT LEFT OUTER JOIN LMCNTV ON
                  LMCNT~MANDT = LMCNTV~MANDT AND
                  LMCNT~CNR = LMCNTV~CNR.

        WRITE: / wa_cnt-cnt-mandt, wa_cnt-cnt-cnr, wa_cnt-vers-cvnr, wa_cnt-cnt-cnt_name, wa_cnt-cnt-cnt_desc.

        APPEND wa_cnt to it_cnt.

      ENDSELECT.

      SELECT * INTO wa_cntv FROM it_cnt LEFT OUTER JOIN LMCNT_INS ON
                  wa_cnt-cnt~MANDT = LMCNT_INS~MANDT AND
                  wa_cnt-cnt~CNR = LMCNT_INS~CNR.

        WRITE: / wa_cntv-wa_cnt-mandt, wa_cntv-wa_cnt-cnr, wa_cntv-wa_cnt-cvnr, wa_cntv-wa_cnt-cnt_name, wa_cntv-wa_cnt-cnt_desc.

      ENDSELECT.

Re: Right outer join alternative ?

former_member129652 — Wed, 18 Jul 2012 05:47:24 GMT

Dear Lokesh Agrawal,

SAP open SQL cannot join internal tables. You should use SE11 (ABAP dictionary) to create a physical table in the database. Save the result of the first join to the physical table.

If the physical table name is "Ztemp_table", you should copy the result of the first join to the internal table it_temp_table and use the following statement to save the result.

DATA: it_temp_table TYPE TABLE OF ztemp_table.

DELETE FROM Ztemp_table.

COMMIT WORK AND WAIT.

MODIFY ztemp_table FROM TABLE it_temp_table.

COMMIT WORK AND WAIT.

Re: Right outer join alternative ?

Former Member — Wed, 18 Jul 2012 15:42:02 GMT

      SELECT * INTO wa_cntv FROM it_cnt LEFT OUTER JOIN LMCNT_INS ON
                  wa_cnt-cnt~MANDT = LMCNT_INS~MANDT AND
                  wa_cnt-cnt~CNR = LMCNT_INS~CNR.

That's not possible in this way, you would've to rewrite it with FOR ALL ENTRIES, like:

SELECT * INTO wa_cntv FROM LMCNT_INS

FOR ALL ENTRIES IN it_cnt

WHERE LMCNT_INS~MANDT = it_cnt-mandt

AND LMCNT_INS~CNR = it_cnt-mandt.

Re: Right outer join alternative ?

Former Member — Thu, 19 Jul 2012 11:26:32 GMT

Hi ,

i think its better to go for all entries instead of joins.

Regards,

venkat.

Re: Right outer join alternative ?

Former Member — Thu, 19 Jul 2012 11:39:12 GMT

I think FOR ALL ENTRIES will return only the matched data from LMCNT_INS table. Additional data of internal table it_cnt cannot be retrieved using FOR ALL ENTRIES.