Result itab empty during a select join

[Former Member]

Hi all,

Iam doing a query as follows to get the following fields:

mast_{matnr mast}stlnr makt_{maktx mara}YYBCEZNDR mvke_{vmsta mvke}prodh marc~Dispo

SELECT mast~matnr mast~stlnr makt~maktx mara~YYBCEZNDR mvke~vmsta mvke~prodh marc~Dispo
   INTO CORRESPONDING FIELDS OF TABLE gt_mmitab FROM mast
      INNER JOIN makt ON mast~matnr = makt~matnr
      INNER JOIN mara ON makt~matnr = mara~matnr
      INNER JOIN mvke ON mara~matnr = mvke~matnr
      INNER JOIN marc ON mvke~matnr = marc~matnr
      WHERE mast~stlan = p_bom
            AND mast~matnr IN so_matnr
            AND mast~werks = '0001'
            AND makt~spras = 'D'.

But the itab(gt_mmitab) is empty.

What am I doing wrong...

Thanks

P

[ThomasZloch]

Most obvious explanation is that your selection criteria are such that no record is found. Since you use inner join, if just one of the involved tables does not have a matching record, the overall result is empty.

Also, your join conditions are incomplete, so you might well end up with too many records after you fixed the initial problem. E.g. you should include WERKS in the join condition of MAST and MARC link MAST and MARC via MATNR and WERKS, and not MVKE and MARC.

Thomas

[matt]

Most likely running it on a client with no data.

[Former Member]

The client has data.It has to do something with my select statement,I gues.Somewhere something is going wrong...

[Former Member]

Does p_bom have a value?

[Former Member]

yep.In this example 8.

[Former Member]

Then that's the problem. Your statement

 WHERE mast~stlan = p_bom 

is the culprit. You might want to define p_bom as required so the value is always filled.

[Former Member]

p_bom is a paremeter field which is always filled.

Thomas,

With this select Iam not trying to get a a pool of records

dont have a system at hand now.

Will check it tommorow after changing the select as follows:

SELECT mast~matnr mast~stlnr makt~maktx mara~YYBCEZNDR mvke~vmsta
mvke~prodh marc~Dispo INTO CORRESPONDING FIELDS OF TABLE gt_mmitab
FROM mast     
      INNER JOIN mara ON mast~matnr = mara~matnr
      INNER JOIN makt ON mara~matnr = makt~matnr
      INNER JOIN mvke ON mara~matnr = mvke~matnr
      INNER JOIN marc ON mara~matnr = marc~matnr
      WHERE mast~stlan = p_bom
            AND mast~matnr IN so_matnr
            AND mast~werks = '0001'
            AND makt~spras = 'D'.

[matt]

As Thomas said - MARC should be joined to MAST, via MATNR and WERKS

SELECT mast~matnr mast~stlnr makt~maktx mara~YYBCEZNDR mvke~vmsta
mvke~prodh marc~Dispo INTO CORRESPONDING FIELDS OF TABLE gt_mmitab
FROM mast     
      INNER JOIN mara ON mast~matnr = mara~matnr
      INNER JOIN makt ON mara~matnr = makt~matnr
      INNER JOIN mvke ON mara~matnr = mvke~matnr
      INNER JOIN marc ON mast~matnr = marc~matnr
                         mast~werks = marc~werks 
      WHERE mast~stlan = p_bom
            AND mast~matnr IN so_matnr
            AND mast~werks = '0001'
            AND makt~spras = 'D'.

You may also be running into conversion exit problems. How is p_bom defined? But essentially, your problem is that your SQL is too complicated to work out easily what is going on.

Here's a bit of programming wisdom. Programmers are expensive. The maintainance of programs is expensive. Processor power is cheap. So, unless there is a real business requirement for absolutely tip top performance, always program for clarity first. Break down your select into simpler units. It will be easier to find out where the failure lies. It will be easier to fix it. And it will be easier for a programmer in the future to fix it. Easier = reduced cost = a good thing .