topic Re: Select statement in Application Development and Automation Discussions

Select statement

Former Member — Mon, 15 Sep 2008 10:47:19 GMT

Hi All,

I am reading data from seven tables and here is my select statement but it returns 4 instead. When i take report to QEC it doesn't execute certain material, please help me out.

SELECT mara_{matnr mara}ernam mara_{aenam mara}mtart mara_{ersda mara}laeda

mara_{vpsta mara}pstat mara_{lvorm mara}zzltxt mara_{meins mara}matkl

mara_{bismt mara}extwg mara_{spart mara}labor mara_{mstae mara}normt

mara_{wrkst mara}ekwsl mara_{mprof mara}stoff marc_{kordb marc}webaz

marc_{kzkri marc}kautb marc_{dispr marc}dispo marc_{dismm marc}disls

marc_{mabst marc}minbe marc_{werks marc}ekgrp marc_{mmsta marc}plifz

marc_{fhori marc}sobsl marc_{mtvfp marc}maabc marc_{prctr marc}eprio

marc_{sernp mard}labst mard_{lgpbe mard}lgort mara_{mbrsh mdma}berid

mdma_{lagpr mdma}plifzx mbew_{bwtty mbew}bklas mbew_{vprsv mbew}verpr

mbew_{peinh mbew}stprs mbew_{bwtar makt}maktx s032_{mbwbest s032}wbwbest

FROM mara JOIN mard ON mara_{matnr = mard}matnr

JOIN marc ON mard_{matnr = marc}matnr

JOIN mdma ON mara_{matnr = mdma}matnr

JOIN mbew ON mara_{matnr = mbew}matnr

JOIN makt ON mara_{matnr = makt}matnr

JOIN s032 ON marc_{werks = s032}werks

INTO CORRESPONDING FIELDS OF TABLE i_tab

WHERE mara~ernam IN s_ernam

AND mara~ersda IN s_ersda

AND mara~aenam IN s_aenam

AND mara~laeda IN s_laeda

AND mara~matnr IN s_matnr

AND marc~werks IN s_werks

AND mard~lgort IN s_lgort

AND mara~mtart IN s_mtart

AND mara~lvorm IN s_lvorm

AND mbew~bklas IN s_bklas

AND mdma~lagpr IN s_lagpr

AND mara~wrkst IN s_wrkst.

SORT i_tab BY matnr.

DELETE ADJACENT DUPLICATES FROM i_tab COMPARING matnr.

Regards,

Fred

Re: Select statement

Former Member — Mon, 15 Sep 2008 10:50:04 GMT

Fantastic

Re: Select statement

Former Member — Mon, 15 Sep 2008 10:52:06 GMT

you shouldn't use join for more than 3 tables...

instead use FOR ALL ENTRIES

Re: Select statement

Former Member — Mon, 15 Sep 2008 10:58:50 GMT

Fred

When you use Inner Join between the tables.... all tables should have corresponding foreign key entries as per the Join.. If any one of those tables doesn't have the entry, it will exclude the record...

I would suggest to go for Left outer join... and you may filter the data further as per the requirement..

Thanks

Amol Lohade

Re: Select statement

Former Member — Mon, 15 Sep 2008 10:59:04 GMT

instead try doing by creating views for this

and use it directly in programs

with regards

s.janagar

Re: Select statement

Former Member — Mon, 15 Sep 2008 11:57:42 GMT

But it doesn't give me the results i want...

Re: Select statement

Former Member — Mon, 15 Sep 2008 11:59:11 GMT

You mean the whole select statement is correct i should only include for all entries, it not please sent me sample code...

Re: Select statement

Former Member — Mon, 15 Sep 2008 12:01:49 GMT

If you can send me the code as im not familiar with left outer join.

Re: Select statement

Former Member — Mon, 15 Sep 2008 12:03:24 GMT

I only use view to check the relationship between the tables, i do not know much about views, please help me with the views.