select with Inner joins not fetching data

[former_member211589]

Hello All,

There is a select which has some inner joins as below:

select **some fields***

INTO CORRESPONDING FIELDS OF TABLE ITAB_MAT

FROM MARA

LEFT OUTER JOIN MAKT

ON MARA_{MATNR = MAKT}MAKTX

LEFT OUTER JOIN MARC

ON MARA_{MATNR = MARC}MATNR

LEFT OUTER JOIN MARD

ON MARA_{MATNR = MARD}MATNR

LEFT OUTER JOIN MBEW

ON MARA_{MATNR = MBEW}MATNR

LEFT OUTER JOIN MARM

ON MARA_{MATNR = MARM}MATNR

LEFT OUTER JOIN MLGT

ON MARA_{MATNR = MLGT}MATNR

WHERE MARA~MATNR IN S_MATNR.

The material has entries in the tables that are used above but still this query is not fetching any record.

what is wrong in this query?

Thanks,

Rakesh.

[Former Member]

Hi Rakesh,

I think the first join condition is wrong. you are trying to compare MATNR and MAKTX. Change the query and try.

ON MARA_{MATNR = MAKT}MAKTX - > ON MARA_{MATNR = MAKT}MATNR..

Regards,

Diwakar

[Former Member]

Hi Rakesh,

You are try to fetch data from 6 tables using join statement. This statement should be used for 4 tables.

First select the data from MARA and then fetch data from other tables using for all entries statement.

Thanks & Regards,

Hemant

[former_member211589]

hI Hemanth,

You mean to say that only 4 joins are allowed in a select query?

Thanks,

Rakesh.

[Former Member]

Hi Rakesh,

Performancewise it would be more effiecient if u use upto four tables.

Anyways on condition in ur query is wrong which may the case u r not getting data.

if should be MARA_{MATNR = MAKT}MATNR

select **some fields***

INTO CORRESPONDING FIELDS OF TABLE ITAB_MAT

FROM MARA

LEFT OUTER JOIN MAKT

ON MARA_{MATNR = MAKT}MAKTX it should be MARA_{MATNR = MAKT}MATNR

LEFT OUTER JOIN MARC

Thanks & regards,

Hemant

[former_member211589]

ya that was a silly mistake and i corrected it.Basically i need fetch some fields of the material master.These fields are there in these tables.so what is the best way to do it?

[Former Member]

Hi Rakesh,

Its a 2 solution answer to your question. (you can use more than 4 tables for inner joins)

1. Lower Performance - because of more tables involved in the query. more data equals lower performance of the query. but you will have all the required data in 1 internal table.

2. Higher performace - split the query into 2 parts and use read statement wheneven you need the details of internal table 2 making the material common in both internal table and using binary search.

I would recomend the 2nd approach as the tables might be huge and might have performance issues.

Thanks,

Diwakar

[Former Member]

Hi Rakesh,

fist of all I'd say there's nothing wrong in principle with your query - apart from the small join error found by Hemant. However, despite the join condition mistake, the query should still return some results because you're only using outer joins. I'm wondering if your range S_MATNR is defined correctly, i.e. if it's of type MATNR and thus all required conversion exits are applied. Otherwise the query might not return any results because you might look for numeric material numbers, but your materials are stored on the database with leading zeros.

So, nothing wrong with your join, if you're using appropriate selection conditions in S_MATNR the query should return materials. If you're in doubt, remove all the joins and do just the select on MARA and see if that works; I'd expect that it behaves the same as your join query.

Note though that since you're only restricting on material number, you'd basically get all the organizational views defined for the given materials, so possibly several entries in ITAB_MAT per material number.

Also, I disagree with Diwakar's statement on performance. In general you actually want to use joins, because this is a far more efficient access than doing individual selects. Sometimes you do encounter problems with joins, because the database optimizer doesn't pick the right execution plan. I.e. when reading the data, the database has to decide in which order the tables should be read, which index is used and how the data should be combined to produce the requested result. If you're joining lots of tables and have several indexes per table this is a tough task and might result in the database picking the wrong approach. However, you can deal with those cases when you see performance is not optimal and supply for example hints to push the database optimizer in the right direction.

Cheers, harald