topic Re: 4 way join... in Application Development and Automation Discussions

4 way join...

Former Member — Mon, 28 Aug 2006 22:17:32 GMT

Hi all,

I have a three way join between MARA, MARC and MAKT that works great. I want to make it a four way join to include two fields from PRPS as follows:

SELECT MAKT~MATNR

MAKT~MAKTX

MARA~MATKL

MARA~MEINS

MARC~WERKS

MARC~DISPO

MARC~FEVOR

PRPS~PSPNR

PRPS~VERNR

INTO CORRESPONDING FIELDS OF TABLE GT_SUPPLY_DEMAND

FROM ( ( ( MAKT

INNER JOIN MARC

ON MARC_{MATNR = MAKT}MATNR )

INNER JOIN MARA

ON MARA_{MATNR = MARC}MATNR )

INNER JOIN PRPS

ON PRPS_{MATNR = MARC}MATNR )

WHERE MAKT~MATNR IN S_MATNR

AND MAKT~SPRAS EQ SY-LANGU

AND MAKT~MAKTX IN S_MAKTX

AND MARA~MATNR IN S_MATNR

AND MARA~MATKL IN S_MATKL

AND MARA~MEINS IN S_MEINS

AND MARC~WERKS IN S_WERKS

AND MARC~DISPO IN S_DISPO

AND MARC~FEVOR IN S_FEVOR

AND PRPS~PSPNR IN S_PSPNR

AND PRPS~VERNR IN S_VERNR.

Here are my questions:

1. matnr is a key in prps. Can I still do a join on that field even though it is not a key?

2. Do I have to include a select on matnr using the select option paramter against every matnr for each table...or can I just select against its origin table of MAKT.

3. Can I even do this

regards,

Mat

Re: 4 way join...

RichHeilman — Mon, 28 Aug 2006 22:48:04 GMT

1) Yes, you can join on any field, but it will decrease the performance if not key fields.

2) No, just one of the fields, in the WHERE clause, preferrable the key field of the top most table.

WHERE MAKT~MATNR IN S_MATNR 
AND MAKT~SPRAS EQ SY-LANGU 
AND MAKT~MAKTX IN S_MAKTX 

<b>*AND MARA~MATNR IN S_MATNR</b> 
AND MARA~MATKL IN S_MATKL 
AND MARA~MEINS IN S_MEINS

AND MARC~WERKS IN S_WERKS 
AND MARC~DISPO IN S_DISPO 
AND MARC~FEVOR IN S_FEVOR 

AND PRPS~PSPNR IN S_PSPNR
AND PRPS~VERNR IN S_VERNR.

3) Sure, you can join as many as you want, but make sure to join correctly, and it is suggested to keep the joins to a lowere number, I'd say 3-4 or less.

Regards,

RIch Heilman

Re: 4 way join...

Former Member — Mon, 28 Aug 2006 23:07:23 GMT

1. yes

2. yes but use this -->

FROM ( ( ( MAKT

INNER JOIN MARC

ON MAkt_{MATNR = MArc}MATNR )

INNER JOIN MARA

ON MAkt_{MATNR = MARa}MATNR )

INNER JOIN PRPS

ON makt_{MATNR = PRPS}MATNR )

WHERE MAKT~MATNR IN S_MATNR "this is enough for validat

AND MAKT~SPRAS EQ SY-LANGU

AND MAKT~MAKTX IN S_MAKTX

AND MARA~MATKL IN S_MATKL

AND MARA~MEINS IN S_MEINS

AND MARC~WERKS IN S_WERKS

AND MARC~DISPO IN S_DISPO

AND MARC~FEVOR IN S_FEVOR

AND PRPS~PSPNR IN S_PSPNR

AND PRPS~VERNR IN S_VERNR.

hope this is usefull...

Re: 4 way join...

Former Member — Tue, 29 Aug 2006 00:03:01 GMT

yes...I thought I tried something like that...but if I enter no values for my select option parameters nothing is returned. Without the fourth join I return all materials. Why does the fourth join cause no values to be returned even though there are values in MARA, MAKT, MARC and PRPS that match?

regards,

Mat

Re: 4 way join...

Former Member — Tue, 29 Aug 2006 00:12:38 GMT

Now I get it...the low and high value for the PRPS fields are both set to 00000000. As a result...nothing comes back

Re: 4 way join...

Former Member — Tue, 29 Aug 2006 11:00:08 GMT

My 4 way join returns no values if my user doesn't set a value for PROJ-VERNR and PROJ-PSPNR. This is because by default the low/high parameters for these selct options are set to all zeros. I thought I could work around this problem by testing for those values and resetting the high end for both to '99999999'....but no luck, still no values returned. I also attempted to embed an IF statemnt in the query that would ignore the PROJ fields as follows:

IF L_IGNORE_PS_SELECT_OPTIONS = 'X'.

INNER JOIN PRPS

ON MAKT_{MATNR = PRPS}MATNR )

ENDIF.

This doesn't work either. Is there another way to work around this issue. Aside from creating to completely seperate queries...one that includes the PROJ fields...and one that does not?

regards,

Mat