How do I find missing entries in outer join table?

[Former Member]

Hi all,

I am trying to find records in table1 that are missing in table2. This is a simple process in SQL, but ABAP is giving me trouble. I want to do this using an outer join.

Example:

Select table1~docnumber

From table1

Left Outer Join table2

On table1_{docnumber = table2}docnumber

Where table2~docnumber IS NULL. (the record is missing in table2)

Note: ABAP gives an error and wants me to use the Having Clause, which is ok, but then ABAP wants me to use Group By, which ok, but then I still get the same syntex error.

Any thoughts on doing this with the outer join and is null options. I do not want to select into two internal tables and compare them.

[Former Member]

I think you are thinking of a sub query. If you press F1 on SELECT, you should see it documented.

Rob

[Former Member]

WHERE clause allows \[NOT] EXISTS condition that you can use.

Syntax 
... \[NOT] EXISTS subquery ... 
Effect 
This expression is true if the resulting set of the subquery subquery contains at least one line (not). 

Sudhi Karkada

<a href="http://main.nationalmssociety.org/site/TR/Bike/TXHBikeEvents?px=5888378&pg=personal&fr_id=10222">Biking for MS Relief</a>

[Former Member]

All,

I am not trying to do a subquery. Just a simple outer join where I know some records are missing in the second table.

Here is the code:

select eban~banfn
 into table i_delay_banfn
 from eban
 left outer join zsmt_prdelay_upd
 on eban~banfn = zsmt_prdelay_upd~banfn
 where zsmt_prdelay_upd~banfn IS NULL.

Here is the error message:

No fields from the right-hand table of a LEFT OUTER JOIN may appear in

the WHERE condition: "ZSMT_PRDELAY_UPD~BANFN".

select eban~banfn
 into table i_delay_banfn
 from eban
 left outer join zsmt_prdelay_upd
 on eban~banfn = zsmt_prdelay_upd~banfn
 having zsmt_prdelay_upd~banfn IS NULL.

**********************************************************

Please use code tags

Edited by: Rob Burbank on Mar 5, 2009 12:20 PM

[Former Member]

OK - how can zsmt_prdelay_upd_{banfn be both null and equal to eban}banfn at the same time?

Rob

[Former Member]

Hi,

Test with this code:

Select table1~docnumber

From table1

Left Outer Join table2

On table2_{docnumber EQ table1}docnumber.

The "table2~docnumber IS NULL" condition is not necessary because it is evaluated in the Left Outer Join statement. The rows that do not agree give back like values NULL.

hope this information is help to you.

Regards,

José

[former_member194613]

> I am not trying to do a subquery. Just a simple outer join where I know some records are missing in the second table.

subqueries are simpler than joins !!!

[former_member194613]

Select table1~docnumber 
From table1
Left Outer Join table2
On table1~docnumber = table2~docnumber
Where table2~docnumber IS NULL.

NULL appears only if there is a record, but one field was never inserted, so it has neither a value nor is it initial.

Select docnumber 
          From table1
          WHERE NOT EXISTS 
                       ( SELECT docnumber FROM table2 ).

Siegfried