Left outer join query

[former_member671009]

Hi Experts,

I am facing a problem with left outer join query. Am using one standard table and ztable for this join. My problem is values are not extracted from the Ztable.

Query:

      SELECT  b~lifnr b~belnr b~gjahr b~xblnr b~shkzg b~blart b~zfbdt b~budat b~wrbtr
         b~wskto b~zlspr s~EXTRACT_STATUS s~maturity_date FROM bsik AS b
         LEFT OUTER JOIN zprm_rvne_sapdoc AS s
         ON s~belnr  EQ  b~belnr
         AND s~gjahr EQ b~gjahr
         INTO CORRESPONDING FIELDS OF TABLE it_join
            WHERE b~zlsch = p_zlsch
            AND b~xblnr IN so_invno
            ORDER BY b~lifnr b~xblnr.

I have all entries of BSIK table in Ztable with extract status as Y but this query is not fetching extract status and maturity date of ztable so it is blank in the internal table.

Need solution.

Regards

Sridevi S

[Former Member]

Hi,

You querry is correct.

I think in the custom table matched records are not found so only you are not getting records.

In this querry in where condition is not satisfying so you are getting zero records.

Try to comment where condition and the check out the result then uncommnet one by one and see the result .

Regards,

Pravin

[Former Member]

Hi,

Check if in the Z table, you are storing in internal format or external format for BELNR. If in the Z table, it is in external format, query will not work.

Hope it helps.

Sujay

[former_member671009]

Hi Sujay,

Can u clearly explain me what is this internal format and external format and how to check this.

Regards

Sridevi S

[Former Member]

are you really sure fieldnames in your ZTABLE and in the structure you want to select into are equal?

[MarcinPciak]

For LEFT OUTER JOIN all records from table B are extracted even no corresponding records from table S are found. So when for given key there is no record in S, you will have these fields left empty (in internal table), although the B table-related fields are filled correctly.

If you want to extract only matching records use INNER JOIN instead.

Regards

Marcin

[former_member671009]

yeah my Ztable has the values(belnr) similiar to bsik table.

I want to use left outer join only. Even if the records doesnt exist in Ztable i want the records from BSIK table.

My problem is for the existing record in Ztable the field am fetching from Ztable(extract_status) is coming as empty.

Edited by: Sridevi Sivanandam on Sep 22, 2010 8:55 AM

[Former Member]

Hi,

While checking if the Z table has correct entries in SE16, Don't check "Check conversion exits" in the settings -> user parameters.

Sujay

[former_member671009]

I tried checking and unchecking the conversion exits but still its not fetching the value

Edited by: Sridevi Sivanandam on Sep 22, 2010 9:52 AM

[former_member671009]

Do we need to maintain any settings in the table for performing join query

[Former Member]

Hi,

see the sample wiki for writing the Left outer join

http://wiki.sdn.sap.com/wiki/display/Snippets/EmployeeInfotype0000to9999ChangeHistory

Specifying Two or More Database Tables as a Left Outer Join

The left outer join, on the other hand, reads lines from the left-hand database table or join even if there is no corresponding line in the right-hand table.

SELECT...

...

FROM <tab> LEFT [OUTER] JOIN <dbtab> [AS <alias>] ON <cond>

<options>

...

<tab> and <dbtab> are subject to the same rules and conditions as in an inner join. The OUTER addition is optional. The tables are linked in the same way as the inner join with the one exception that all lines selected from <tab> are included in the final selection. If <dbtab> does not contain any lines that meet the condition <cond>, the system includes a single line in the selection whose columns from <dbtab> are filled with null values.

In the left outer join, more restrictions apply to the condition <cond> than in the inner join. In addition to the above restrictions:

EQ or = is the only permitted relational operator.

There must be at least one comparison between columns from <tab> and <dbtab>.

The WHERE clause may not contain any comparisons with columns from <dbtab>. All comparisons using columns from <dbtab> must appear in the condition <cond>.

If we have two tables named stud1,stud2 with the following data

Stud1: id Name stud2: id Name

1 xxx 1 aaa

2 yyy 2 bbb

3 zzz 4 ccc

4 www 6 ddd

When we use Left Outer Join we get the output as:

1 aaa

2 bbb

3 <Null>

4 ccc

When we use Right Outer Join we get the output as:

1 aaa

2 bbb

4 ccc

<Null> ddd

When we use Full Outer Join we get the output as:

1 aaa

2 bbb

3 <Null>

4 ccc

<Null> ddd

Prabhudas

[Former Member]

Hi,

I tested your query using tables BSIK and BKPF and it is picking data correctly. There is issue in the way BELNR is stored in your Z TABLE. As we explained, check if the value in your BELNR field in the Z table has internal / external format.

Hope it helps.

Sujay

[former_member671009]

The field conversion exists checkbox is unchecked in my case. But still am not getting values from the ztable.

[Former Member]

Hi,

There's nothing wrong with the select query. Maybe you do not have matching records in both the standard and the Ztable.

Check once again removing the where conditions.

[former_member671009]

Hi all,

Thanks. The issue is fixed. The problem was not in the query but in structure of it_join where the field name didnt match with the query.

Regards

Sridevi S