Help with my first INNER JOIN

[Former Member]

I was hoping to get some help with my first inner join. The following will be used as input:

p_bwart, p_matkl, p_spras, p_budat, p_plant. I am attempting to find all material numbers and quantities that were shipped in a certain date range. Also these materials must be in the desired material group and plant.

SELECT a~mblnr a~jmahr b~bwart b~matnr b~werks c~matkl c~matart d~maktx
  INTO CORRESPONDING FIELDS OF TABLE itab
  FROM ( ( ( mkpf AS a
    INNER JOIN mseg AS b ON b~mblnr = a~mblnr
                          AND b~mjahr = a~mjahr
                          AND a~budat IN p_buda
                          AND b~bwart = p_bwart
                          AND b~plant = p_plant )
    INNER JOIN mara AS c ON c~matnr = b~matnr
                          AND c~matkl = p_matkl )
    INNER JOIN makt AS d ON d~matnr = c~matnr
                          AND d~spras = p_spras ).

Regards,

Aaron

[Former Member]

I was hoping to get some help with my first inner join. The following will be used as input:

p_bwart, p_matkl, p_spras, p_budat, p_plant. I am attempting to find all material numbers and quantities that were shipped in a certain date range. Also these materials must be in the desired material group and plant.

SELECT a~mblnr a~jmahr b~bwart b~matnr b~werks c~matkl c~matart d~maktx
  INTO CORRESPONDING FIELDS OF TABLE itab
  FROM ( ( ( mkpf AS a
    INNER JOIN mseg AS b ON b~mblnr = a~mblnr
                          AND b~mjahr = a~mjahr
                          AND a~budat IN p_buda
                          AND b~bwart = p_bwart
                          AND b~plant = p_plant )
    INNER JOIN mara AS c ON c~matnr = b~matnr
                          AND c~matkl = p_matkl )
    INNER JOIN makt AS d ON d~matnr = c~matnr
                          AND d~spras = p_spras ).

Regards,

Aaron

[Former Member]

Hi,

Please try this.

SELECT a~mblnr a~jmahr b~bwart b~matnr b~werks c~matkl c~matart d~maktx
INTO CORRESPONDING FIELDS OF TABLE itab
FROM mkpf AS a
INNER JOIN mseg AS b ON b~mblnr = a~mblnr
                    AND b~mjahr = a~mjahr
INNER JOIN mara AS c ON c~matnr = b~matnr
INNER JOIN makt AS d ON d~matnr = c~matnr
WHERE a~budat IN p_buda
  AND b~bwart = p_bwart
  AND b~plant = p_plant
  AND c~matkl = p_matkl 
  AND d~spras = p_spras.

Regards,

Ferry Lianto

[Former Member]

Ok, that looks much better. I wasn't sure where (or how) I needed to include the user input into the select statement.

Thanks again,

Aaron

[RichHeilman]

I fear you may see some poor performance here.

report zrich_0001.

tables: mkpf.

data: begin of itab occurs 0,
      mblnr type mkpf-mblnr,
      mjahr type mkpf-mjahr,
      bwart type mseg-bwart,
      matnr type mseg-matnr,
      werks type mseg-werks,
      maktl type mara-matkl,
      mtart type mara-mtart,
      maktx type makt-maktx,
      end of itab.

 select-options: s_budat for mkpf-budat.
 parameters: p_bwart type mseg-bwart,
             p_werks type marc-werks,
             p_matkl type mara-matkl,
             p_spras type makt-spras.


select a~mblnr a~mjahr b~bwart b~matnr b~werks c~matkl c~mtart d~maktx
  into corresponding fields of table itab
  from mkpf as a
    inner join mseg as b
          on b~mblnr = a~mblnr
         and b~mjahr = a~mjahr
    inner join mara as c
          on c~matnr = b~matnr
    inner join makt as d
          on d~matnr = c~matnr
        and d~spras = p_spras
                 where  a~budat in s_budat
                   and b~bwart = p_bwart
                   and b~werks = p_werks
                   and c~matkl = p_matkl
                   and d~spras = p_spras.

Regards,

Rich Heilman

[Former Member]

Rich, this is something that will be run as a background job because I still have to load a table with all of the material numbers and the amount shipped (of each material) for 366 days. Then I have to calculate the top 5 consecutive shipping days for each material. I assumed that the Select statement wouldn't be very good in performance but I figured the whole report will take a while to run (well over 15 minutes). Do you think the performance will be so bad that I should take another approach?

[RichHeilman]

I just tested my example above and it is not as bad as I first thought. Just make sure that you have a selection in the S_BUDAT, I think all will be well in the background.

Regards,

Rich Heilman

[Former Member]

Rich, thanks for the reply! I was first going to use three select statements and three tables but I figured the inner join would be much cleaner looking and that it would perform a bit better. Thanks again for your input!

Aaron

[Former Member]

Rich, what happens if I have a select-options statement for a few fields in the select statement and values weren't entered for one or more fields? Basically I need select-options for most of my inputs but they will not all be used at once. How do I handle one that isn't being used?

Message was edited by:

Aaron Shover

[RichHeilman]

This is what I was talking about eariler, about fearing bad performance, its hard to tell what would happen, it may be ok as long as you have a specific value(or range) for the S_BUDAT, it seems that it is using this as the key during selection. Convert the rest to SOs, and start experimenting.

Try to avoid any "Not Equal To" in the select options.

Regards,

Rich Heilman

[Former Member]

Rich, can you do an in-line case statement in open SQL?

Regards,

Aaron

[RichHeilman]

You mean like this?

report zrich_0001.

data: it0011 type table of t001.
data: it0012 type table of t001.

data: xt001 type t001.

select * into xt001 from t001.


  case xt001-bukrs.
    when '0010' or '0020'.
      append xt001 to it0011.

    when others.
      append xt001 to it0012.
  endcase.

endselect.

Yes, but it is not advisable to use SELECT ENDSELECT as it is bad for performance, every time thru the LOOP it is a trip to the database.

Regards,

Rich Heilman

[Former Member]

Rich, I have one last question. Will this select give me the following (if not what does it give me)

a table with repeating document numbers (one entry for each material in the document)?

Regards,

Aaron

[RichHeilman]

I assume that you are referring to your original SELECT, and yes you will get all line items of the material document.

Regards,

Rich Heilman

[Former Member]

Yes I am talking about my original select. Thanks for that input. I thought that is what I would get but when I sat back and looked at it a second time I confused myself (it is all of the joins).

Thanks again for all of your help!

Aaron

[RichHeilman]

I know what you mean, I actually have a SELECT statement in a report program which JOINs 13 different tables. I did this before I learned that having that many joins may not be good idea for performance reasons, but it just so happens that the SELECT works really well(fast) with the selection criteria on the selection-screen, so I never changed it, but yes, it can become confusing.

Regards,

Rich Heilman