how to avoid data repeated in report?

[Former Member]

Hi experts,

I'm working on a report that needed

plant,

new material code,

old material code,

material type,

special stock and

(material status ne '01')

select p~werks m~matnr m~bismt m~mtart s~sobkz
  into corresponding fields of table it_mat
  from ( ( MARC as p inner join MARA as m on p~matnr = m~matnr )
inner join MSEG as s on s~matnr = p~matnr )
  where m~matnr in p_mcode
  and m~bismt in p_ocode
  and m~mtart in p_mtype
  and p~werks in p_plant
  and m~matkl in p_mgroup
  and p~mmsta ne '01'
  and s~sobkz in p_spec
  order by m~matnr.
  APPEND it_mat.

This is my select stament ,

and it's output layout the material code are all repeating twice or 3-5 times for the same data.

Is there anyway to take out the repeated data?

Please advice.

Thank you in advanced.

[Former Member]

Hi,

Use

ORDER BY <field> ASCENDING.

DELETE ADJACENT DUPLICATES FROM it_mat.

it will delete all duplicates & repeated data.

Regards

Hema

[Former Member]

Hi,

Use ,

select p_{werks m}matnr m_{bismt m}mtart s~sobkz

into corresponding fields of table it_mat

from ( ( MARC as p inner join MARA as m on p_{matnr = m}matnr )

inner join MSEG as s on s_{matnr = p}matnr )

where m~matnr in p_mcode

and m~bismt in p_ocode

and m~mtart in p_mtype

and p~werks in p_plant

and m~matkl in p_mgroup

and p~mmsta ne '01'

and s~sobkz in p_spec

order by m~matnr ASCENDING.

DELETE ADJACENT DUPLICATES FROM it_mat.

APPEND it_mat.

it will delete all duplicates & repeated data.

Regards

Hema

[Former Member]

Hi hema,

Thank you so much, it's solve ^.*

Thanks to vijay and chandra too.

I've rewards marks to you ya.

Thanks a lots !

[Former Member]

You are most Welcome,

Hema

[Former Member]

add werks also in JOIN on MSEG.

select p~werks m~matnr m~bismt m~mtart s~sobkz
  into corresponding fields of table it_mat
  from ( ( MARC as p inner join MARA as m on p~matnr = m~matnr )
inner join MSEG as s on s~matnr = p~matnr  and
                                     s~werks = p~werks
)
  where m~matnr in p_mcode
  and m~bismt in p_ocode
  and m~mtart in p_mtype
  and p~werks in p_plant
  and m~matkl in p_mgroup
  and p~mmsta ne '01'
  and s~sobkz in p_spec
  order by m~matnr.
  APPEND it_mat.

[Former Member]

Hi Milka,

Try the following:

ORDER BY m~matnr ASCENDING.

DELETE ADJACENT DUPLICATES FROM it_mat.

Regards,

Chandra Sekhar