topic Re: Left outer join query in Application Development and Automation Discussions

Left outer join query

former_member671009 — Wed, 22 Sep 2010 04:47:46 GMT

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

Re: Left outer join query

Former Member — Wed, 22 Sep 2010 06:10:31 GMT

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

Re: Left outer join query

Former Member — Wed, 22 Sep 2010 06:34:51 GMT

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

Re: Left outer join query

former_member671009 — Wed, 22 Sep 2010 06:48:31 GMT

Hi Sujay,

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

Regards

Sridevi S

Re: Left outer join query

Former Member — Wed, 22 Sep 2010 06:51:25 GMT

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

Re: Left outer join query

MarcinPciak — Wed, 22 Sep 2010 06:53:54 GMT

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

Re: Left outer join query

former_member671009 — Wed, 22 Sep 2010 06:54:11 GMT

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

Re: Left outer join query

Former Member — Wed, 22 Sep 2010 06:59:57 GMT

Hi,

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

Sujay

Re: Left outer join query

former_member671009 — Wed, 22 Sep 2010 07:04:35 GMT

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

Re: Left outer join query

former_member671009 — Wed, 22 Sep 2010 08:58:40 GMT

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

Re: Left outer join query

Former Member — Wed, 22 Sep 2010 09:09:02 GMT

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>

...

<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

Re: Left outer join query

Former Member — Wed, 22 Sep 2010 09:21:05 GMT

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

Re: Left outer join query

former_member671009 — Wed, 22 Sep 2010 09:34:25 GMT

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

Re: Left outer join query

Former Member — Thu, 23 Sep 2010 05:24:00 GMT

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.

Re: Left outer join query

former_member671009 — Thu, 23 Sep 2010 05:33:50 GMT

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