Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

left outer join problem

Former Member
0 Kudos

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.

9 REPLIES 9

Former Member
0 Kudos

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.

0 Kudos

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.




0 Kudos

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

ThomasZloch
Active Contributor
0 Kudos

What is your desired result?

0 Kudos

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,

0 Kudos

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

0 Kudos

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

0 Kudos

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.

0 Kudos

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.