Select Statment Error (SQL)

[Former Member]

Dear Friends,

  LOOP AT IT_ZSD012_001 INTO WA_ZSD012_001.
    SELECT  A~MATNR A~LABST A~UMLME A~INSME A~EINME A~SPEME A~RETME B~MTART
      APPENDING TABLE IT_INVENTORY
      FROM  MARD AS A
      INNER JOIN MARA AS B ON B~MATNR EQ A~MATNR
      WHERE A~MATNR = '1.1.1'
      and   A~WERKS EQ  WA_ZSD012_001-PARAM_1
      AND   A~LGORT EQ  WA_ZSD012_001-VALUE
      AND   A~LABST GT 0
      OR    ( A~UMLME GT 0
      OR    A~INSME GT 0
      OR    A~EINME GT 0
      OR    A~SPEME GT 0
      OR    A~RETME GT 0 )
      AND   B~MTART IN ('ZFG1' , 'ZFG4' , 'ZR12' ).
  ENDLOOP.

this is my code (select statement)

but this is not working, when i execute this so many materials came to the itab it should come only "1.1.1"

my requirement is i want to get data from MARD agaist WERKS, LGORT and also MARA-MTART in that groups.

its came with zero inventory, i want to avid them how can i do this.

hope you can understand what i an said?

Thanks in advanced

[aris_hidalgo]

Hi Nelson,

First, you can try to remove your select statement outside the loop since this will result in slow performance and just use FOR ALL ENTRIES. Check first the values you compare against in the tables if how many records are there or if there is none.

Thanks.

[Former Member]

when i execute this so many materials came to the itab it should come only "1.1.1"

You mean different material numbers are coming or same material 1.1.1 is coming number of times.

[Former Member]

Hi

There are 2 problems .

One is use for all entries . Second is that since ur using AND , OR in the same select it will consider

the conditions of AND and the conditions of OR .

Jus rephrase ur select as :

SELECT A_{MATNR A}LABST A_{UMLME A}INSME A_{EINME A}SPEME A_{RETME B}MTART

APPENDING TABLE IT_INVENTORY

FROM MARD AS A

INNER JOIN MARA AS B ON B_{MATNR EQ A}MATNR

WHERE A~MATNR = '1.1.1'

and A~WERKS EQ WA_ZSD012_001-PARAM_1

AND A~LGORT EQ WA_ZSD012_001-VALUE

AND ( A_{LABST GT 0 OR A}UMLME GT 0 OR A_{INSME GT 0 OR A}EINME GT 0

OR A_{SPEME GT 0 OR A}RETME GT 0 )

AND B~MTART IN ('ZFG1' , 'ZFG4' , 'ZR12' ).

This should work !

[Former Member]

Hi nelson,

For performance issue never do a select statement into a loop. wat you can do is fill an internal table and then read internal table inside your loop.

For your problem try this alternative.

select mard_{matnr mard} ....... into table it_inventory

from mard

inner join mara on mara_{matnr eq mard}matnr

for all entries in it_2sd012

where (conditions).

this should solve your problem.

cheers

kevin

[Former Member]

Hi,

Make this change,

LOOP AT IT_ZSD012_001 INTO WA_ZSD012_001.

SELECT A_{MATNR A}LABST A_{UMLME A}INSME A_{EINME A}SPEME A_{RETME B}MTART

APPENDING TABLE IT_INVENTORY

FROM MARD AS A

INNER JOIN MARA AS B ON B_{MATNR EQ A}MATNR

WHERE A~MATNR = '1.1.1'

and A~WERKS EQ WA_ZSD012_001-PARAM_1

AND A~LGORT EQ WA_ZSD012_001-VALUE

AND A~LABST GT 0

AND ( A~UMLME GT 0 -

---

>change OR from AND

OR A~INSME GT 0

OR A~EINME GT 0

OR A~SPEME GT 0

OR A~RETME GT 0 )

AND B~MTART IN ('ZFG1' , 'ZFG4' , 'ZR12' ).

ENDLOOP.

According to ur select query, OR conditions should be inside the brackets ( ).

When u put OR condition outside of the bracket. if any one condition gets true, it'll fetch that record that's why u r getting multiple record. If u use AND, it'll fetch the record onlyif both the condition are true.

Just check the above select query.

[Former Member]

Hi Nelson,

For better performance try to avoid using join statements. Use for all entries instead.

Never use select staments in Loops.

try your code following way

if it_zsd012_001 is not initial.
  
   select matnr labst umlme insme einme speme retme 
      from mard
      appending table it_inventory
      for all entries in it_asd012_001
      where matnr = '1.1.1'
      and   werks = it_asd012_001-param_1
      and   lgort = it_asd012_001-value
      and  (labst gt 0 or insme gt 0 or einme gt 0 or speme gt 0 or retme gt 0).

       if sy-subrc eq o.
            select mtart from mara
                   into table it_mara 
                    for all entries in it_inventory
                    where matnr = it_enventoty-matnr
                    and   mtart in ('ZFG1' , 'ZFG4' , 'ZR12' ).
    
        endif.
        endif.

"Then just merge two table into third one.

Hope it will help to solve your query,

Pratik

[Former Member]

Dear Friends

Thanks for your help it solved.

I know there are some performance issue but i unable to use for all entries because there are some type mismatch.

Anyhow thanks again.

Have you a nice day!