topic JOIN in select query in Application Development and Automation Discussions

JOIN in select query

Former Member — Wed, 05 Nov 2008 09:20:31 GMT

Hi All,

I need to do an enhancement in the following Select query.

SELECT

MARA data

a~matnr

MARC data

b~werks

MARA data

a~normt

a~ean11

a~labor

a~mstae

a~mstde

a~bismt

a~spart

a~zzsc

a~zzmc

a~zzpc

a~zzbu

a~zz4tpc

a~zz6tpc

a~kosch

a~iprkz

g~bmatn

a~normt

g~mfrnr

a~bstme

a~zeifo

a~ersda

MARC data

b~mtvfp

b~sernp

b~herkl

b~ekgrp

b~dispo

b~insmk

b~disgr

b~ladgr

b~lgfsb

b~mtver

b~perkz

b~eprio

b~wzeit

MVKE data

c~dwerk

c~kondm

c~bonus

c~mtpos

c~provg

c~mvgr1

c~mvgr2

c~mvgr3

c~prat9

c~prat1

MBEW data

d~bklas

d~stprs

INTO TABLE i_materials

FROM ( marc as b

inner jOIN mara AS a ON a_{matnr = b}matnr

inner JOIN mvke AS c ON c_{matnr = a}matnr

inner JOIN ampl AS g ON g_{bmatn = a}matnr

LEFT OUTER JOIN mbew AS d ON d_{matnr = b}matnr

AND d_{bwkey = b}werks )

WHERE a~matnr IN s_matnr

AND a~normt IN s_normt

AND a~mstae IN s_mstae

AND a~zz4tpc IN s_zz4tpc

AND a~zz6tpc IN s_zz6tpc

AND a~bismt IN s_bismt

AND a~iprkz IN s_iprkz

AND a~spart IN s_spart

AND a~zzsc IN s_zzsc

AND a~zzpc IN s_zzpc

AND a~zzmc IN s_zzmc

AND a~zzbu IN s_zzbu

AND a~ersda IN s_ersda

MARC Data

AND b~werks IN s_werks

AND b~ekgrp IN s_ekgrp

AND b~disgr IN s_disgr

AND b~perkz IN s_perkz

AND b~sernp IN s_sernp

AND b~ladgr IN s_ladgr

AND b~mtvfp IN s_mtvfp

AND b~lgfsb IN s_lgfsb

AND b~mtver IN s_mtver

AND b~eprio IN s_eprio

AND b~wzeit IN s_wzeit

AND b~herkl IN s_herkl

MVKE data

AND c~mtpos IN s_mtpos

AND c~kondm IN s_kondm

AND c~bonus IN s_bonus

AND c~provg IN s_provg

AND c~mvgr1 IN s_mvgr1

AND c~mvgr2 IN s_mvgr2

AND c~mvgr3 IN s_mvgr3

AMPL Data

AND g~reoam <> 'X'.

This query will pick the materials only if it is maintained across all the tables MARA,MARC,MVKE,AMPL,MBEW.

Now the requirement is, the program should pick the materials that are maintained in MARA table, doesn't matter if the material is not maintained in the other tables.

Please tell me how to modify this Select query. I dont have any idea on JOINs.

Thanks in Advance,

Neethu.

Re: JOIN in select query

Former Member — Wed, 05 Nov 2008 09:22:45 GMT

Select from the table MARA first and then use a LEFT OUTER JOIN on all the other tables (MARC, MVKE...)

Re: JOIN in select query

Former Member — Wed, 05 Nov 2008 09:23:37 GMT

remove the 'Joins'

and remove the fields no longer required from teh where clause ex any field other than A~.

Re: JOIN in select query

Sandeep_Panghal — Wed, 05 Nov 2008 09:24:50 GMT

You can directly write a simple select on MARA with WHERE clause .

Re: JOIN in select query

Former Member — Wed, 05 Nov 2008 09:35:04 GMT

Hi Manoj,

If possible please tell me how to use left outer join in the select query. Please provide the query

Thanks,

Neethu

Re: JOIN in select query

Former Member — Wed, 05 Nov 2008 09:37:42 GMT

You can use this sample piece of code with suitable modifications:

SELECT
<fields...>
FROM mara AS a
LEFT OUTER JOIN marc AS b
ON a~matnr EQ b~matnr
LEFT OUTER JOIN mvke AS c
ON a~matnr EQ c~matnr
...

Re: JOIN in select query

Former Member — Wed, 05 Nov 2008 11:17:47 GMT

Hi Manoj,

I have modified the query like

select fields

FROM ( mara as a

left outer join marc AS b ON a_{matnr = b}matnr

left outer Join mvke AS c ON a_{matnr = c}matnr

left outer Join ampl AS g ON a_{matnr = g}bmatn

left outer Join mbew AS d ON a_{matnr = d}matnr )

WHERE a~matnr IN s_matnr

AND a~normt IN s_normt

AND a~mstae IN s_mstae

AND a~zz4tpc IN s_zz4tpc

AND a~zz6tpc IN s_zz6tpc

AND a~bismt IN s_bismt

AND a~iprkz IN s_iprkz

AND a~spart IN s_spart

AND a~zzsc IN s_zzsc

AND a~zzpc IN s_zzpc

AND a~zzmc IN s_zzmc

AND a~zzbu IN s_zzbu

AND a~ersda IN s_ersda

MARC Data

AND b~werks IN s_werks

AND b~ekgrp IN s_ekgrp

AND b~disgr IN s_disgr

AND b~perkz IN s_perkz

AND b~sernp IN s_sernp

AND b~ladgr IN s_ladgr

AND b~mtvfp IN s_mtvfp

AND b~lgfsb IN s_lgfsb

AND b~mtver IN s_mtver

AND b~eprio IN s_eprio

AND b~wzeit IN s_wzeit

AND b~herkl IN s_herkl

MVKE data

AND c~mtpos IN s_mtpos

AND c~kondm IN s_kondm

AND c~bonus IN s_bonus

AND c~provg IN s_provg

AND c~mvgr1 IN s_mvgr1

AND c~mvgr2 IN s_mvgr2

AND c~mvgr3 IN s_mvgr3

AMPL Data

AND g~reoam <> 'X'.

I am getting the error, "No fields from the right-hand table of a LEFT OUTER JOIN may appear in the WHERE condition:"B~WERKS".

Now prob. is with WHERE condition. If you know the solution please let me know.