topic left outer join problem in Application Development and Automation Discussions

left outer join problem

Former Member — Mon, 24 Feb 2014 15:58:46 GMT

Hi all,

Below is my code:


  SELECT a~column1
               a~column2
               b~column3
               b~year
      INTO CORRESPONDING FIELDS OF TABLE itab
      FROM ztab1 AS a
      LEFT OUTER JOIN ztab2 AS b
      ON a~column1 = b~column1 AND
         a~column2 = b~column2 AND
         b~year =  sy-datum+0(4).

This outer join is picking all the entries where the date is '000000'.

Please let me know what is wrong.

Re: left outer join problem

Former Member — Mon, 24 Feb 2014 16:11:53 GMT

if you use a left join, it loads all left-handed records from the table and does not care if right-handed conditions are not met. So this is what you really wanted. In other languages (not inside SAP), you would get null values on the records of the right tab. SAP does not work well with null-s, so it uses initial values - that is the reason, why you see '0000' in the year column.

Re: left outer join problem

Former Member — Mon, 24 Feb 2014 16:18:41 GMT

If that was the case, then how come SAP provided the below example in its Help documentation:

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.

Re: left outer join problem

ThomasZloch — Mon, 24 Feb 2014 16:21:23 GMT

What is your desired result?

Re: left outer join problem

Former Member — Mon, 24 Feb 2014 16:22:11 GMT

As you may see in the example:

IF wa-connid = '0000'.
WRITE: / wa-carrid, wa-carrname.

ENDIF.

it checks, whether there is a value of connid, took from the right table. Once the value is initial ('0000'), the record is still fetched, but the right side (connid) could not be found. I do not see, what is wrong with this explanation

Re: left outer join problem

Former Member — Mon, 24 Feb 2014 16:31:39 GMT

Hi Thomas,

There are two tables ztab1 and ztab2.

I need to outer join ztab1 and ztab2.

But all the resulting records should be of this year.

The column for year is in ztab2.

Rgds,

Re: left outer join problem

Former Member — Mon, 24 Feb 2014 16:33:28 GMT

You need to make changes to your coding as you can't specify conditions on the right-handed table

Re: left outer join problem

ThomasZloch — Mon, 24 Feb 2014 16:38:18 GMT

Why do you want to outer join, when you have a specific condition for table ztab2, which only records that are present in that table can fulfill?

Try changing this into an inner join, or please explain how an outer join and your selection condition should work together.

Thomas

Re: left outer join problem

Former Member — Mon, 24 Feb 2014 17:03:54 GMT

There are two tables ztab1 and ztab2 are supposed to have exact number of entries.

There are two common key fields between 2 tables. col1 and col2.

As i have mentioned earlier only ztab2 has the month and year columns.

Requirement: For a given specific month and year, i need to check if there are any entries missing in the ztab2 for a given year. If so need to retrieve those missing entries from ztab1.

Re: left outer join problem

Former Member — Tue, 25 Feb 2014 07:52:22 GMT

if you only need the missing records, then your select can be left alone like it is. If the year column is obligatory, then selecting as you are you will have a larger data-set with all records from the left table. Just loop all the records and compare year column, if it has the value '0000', then it is your output.