Left Outer Join - Mismatch in Result

[Former Member]

Hi,

I'm trying to get details of the Customers who are not having sales document using standard tables KNA1 and VBAK to display records.

Inner join displays 236 records and Left-outer join returns 241 records, that means 5 Customers are there without sales document.

If I use the following code :

SELECT KNA1~KUNNR KNA1~LAND1 KNA1~NAME1 VBAK~VBELN VBAK~ERDAT VBAK~NETWR
INTO TABLE ITAB
FROM KNA1 LEFT OUTER JOIN VBAK
ON KNA1~KUNNR = VBAK~KUNNR
WHERE KNA1~KUNNR IN CUST
ORDER BY KNA1~KUNNR.


LOOP AT ITAB WHERE VBELN IS INITIAL.

Where Cust is a select-option to take input Customer Number Low and High value. This code returns the correct value i.e 5.

Whereas if I use

SELECT KNA1~KUNNR KNA1~LAND1 KNA1~NAME1 VBAK~VBELN VBAK~ERDAT VBAK~NETWR
INTO TABLE ITAB
FROM KNA1 LEFT OUTER JOIN VBAK
ON KNA1~KUNNR = VBAK~KUNNR AND VBAK~VBELN EQ 0
WHERE KNA1~KUNNR IN CUST
ORDER BY KNA1~KUNNR.


LOOP AT ITAB.

displays 8 records.

What I'm trying to do is, the Select statement only to fetch the number of records based on the condition where there is no sales document for the particular customer rather then getting all the records into internal table and then looping at no sales document.

Thanks in advance.

[Former Member]

Moderator message - Welcome to SCN. You might try a sub-query rather than a JOIN here. Rob

[Former Member]

As Rob suggests, use a subquery

Read KNA1, subquery VAKPA for KNA1-KUNNR and correct PARVW value...If found, proceed with next KNA1, if not found, write the KNA1 data to an internal table.

See SE30 tips and tricks for how to do a subquery.

[Former Member]

Thanks Rob and Breakpoint, but I would like to know why would JOIN query will not work.

[Former Member]

inner join - retain where table-field values are equal...

left outer join - retain all of left whether or not matched on the right...

[Former Member]

Yeah that should be the reason, so even though I wanted to restrict the number of records without sales doc through select query, Left outer join wouldn't work.

Thanks BreakPoint.