topic Re: Select Inner join condition problem on item in Application Development and Automation Discussions

Select Inner join condition problem on item

Former Member — Sun, 16 Mar 2008 21:38:43 GMT

We have two tables: Z_table1 and Z_table2:

In Z_table1, there is the document field EBELN and item field EBELP (type as NUMC and the length is 5).

In table Z_table2, there is the document field EBELN and the item field EBELP (type as CHAR and the length is 3)

If we use the following select by just inner joining based on the PO document level, then it works fine to get records into itab:


select   b~field1
            c~field2
            from Z_table1 as b
            inner join Z_table2 as c
            on b~EBELN = c~EBELN
            into corresponding fields of table itab where ...

But if we use the following select statement by using inner joinning on both PO document and item level, then it returns nothing into itab:


select   b~field1
            c~field2
            from Z_table1 as b
            inner join Z_table2 as c
            on b~EBELN = c~EBELN and b~EBELP = c~EBELP
            into corresponding fields of table itab where ...

By checking these above two tables, they do have the common PO document and item number records, e.g., in 1st table, there is PO 1001 and it's item 00001 (length 5 of NUMC) and in 2nd table, there is PO 1001 and it's item 001 (length 3 of CHAR). But why the 2nd above select by employing the inner join on item condition not working successfully to get data into itab? Any idea?

Thanks and we will give you reward points!

Re: Select Inner join condition problem on item

Former Member — Sun, 16 Mar 2008 23:32:03 GMT

Hi Kevin,

Its simply because of the mismatch of the data..as data stored in different format (different lengths also).

In your scenario you should have join based on ebeln only and then sort based on ebelp.

Regards,

Atish

Re: Select Inner join condition problem on item

Former Member — Mon, 17 Mar 2008 00:47:56 GMT

hi Atish,

But in our select statement, we don't want to select EBELN and EBELP that the sort won't work. Is there a way of we can still use the inner join condition with the item EBELP but need to convert one type to another and then to be employed in the condition?

Would be appreciated if you could give the select statement example on how to do this!

Thanks!

Re: Select Inner join condition problem on item

Former Member — Mon, 17 Mar 2008 01:27:48 GMT

Hi Kevin,

It will not be possible to get the correct result using JOIN in the select statement in your case.

Instead of JOIN you can use FOR ALL ENTRIES.

Regards,

Atish

Re: Select Inner join condition problem on item

Former Member — Mon, 17 Mar 2008 02:09:26 GMT

hi Atish Sarda,

Could you show the sample code on how to use the FOR ALL ENTRIES to get the workaround for the type mismatch in the inner join condition for the item level?

Thanks in advance!