Select Query Problem

[Former Member]

Hi All,

I am selecting values from a table zexc_rec using the query

select * from zexc_rec into table it_ZEXC_REC

where

LIFNR in S_LIFNR and

DOCNO in S_DOCNO and

RDOCTYP in S_RDTYP and

DOCTYP EQ S_DOCTYP AND

DATE1 in S_DATE1 and

MATNR EQ S_MATNR.

Now I am having condition that I have to check whether any other row with same document no., same material no and document type as ' CT ' exist there in the table zexc_rec, if it is there then I have to select the row with the doctyp as ' CT' otherwise I have to select the row with doctype as 'CT1'.

Please suggest what modifications I should do?

Thanks.

[Former Member]

Hi,

Try as follows:

select * from zexc_rec into table it_ZEXC_REC

where

LIFNR in S_LIFNR and

DOCNO in S_DOCNO and

RDOCTYP in S_RDTYP and

DOCTYP EQ S_DOCTYP AND

DATE1 in S_DATE1 and

MATNR EQ S_MATNR

<b> and doctyp in ( 'CT' , 'CT1' ).

sort table it_zexc_rec by matnr.

delete adjacent duplicates from it_zexc_rec comparing matnr.

</b>.

Regards

Subramanian

[Former Member]

Thanks a lot Subramanian ,

Can u please tell how to delete the adjacent duplicates by comparing matnr.

[Former Member]

Hi Rahul ,

As a contuination of my previous post , i have a doubt , in the select there is a field RDOCTYP in the where clause , is this the document type CT , CT1.

Regards

Arun

[Former Member]

Hi Rahul,

Just copy this code in SE38 and run it , you will understand how to delete the adjacent duplicates by comparing a key field.

<b>report zkun_del1 .

data : begin of warea ,

a type i,

b type c,

m type i,

end of warea.

data : j type i.

data itab like table of warea with header line.

j = 5 .

while j > 0.

itab-a = itab-a + 1.

itab-b = 'b'.

itab-m = itab-m + 1.

append itab.

j = j - 1.

endwhile.

write 78.

loop at itab.

write:/ itab-a, itab-b , itab-m.

endloop.

itab-a = itab-a + 1.

itab-b = 'c'.

itab-m = itab-m + 1.

append itab.

<i><b>delete adjacent duplicates from itab comparing b.</b></i>

loop at itab.

write:/ itab-a, itab-b , itab-m.

endloop.</b>

Here, I have defined an internal table . Then I have created some entries in internal table and print them.

After that I have delted the adjacent duplicates and again print the output.

In the similar way, you can use this concept for your pupose as :

<i><b>delete adjacent duplicates from <internal table name> comparing matnr.</b></i>

Thanks and Regards,

Kunal

[Former Member]

Hi,

Are the document number (LIFNR) and doc type (DOCTYP) both primary keys in the table 'zexc_rec'..

If so u will get only one value..

I suppose that LIFNR is surely a key field..

If DOCTYP is not a key field , mention both values CT and CT1 in the selection parameters S_DOCTYP ...

This will fetch all the records for the document number with all the corresponding doc types..

Thanks

Rajiv

[Former Member]

Hi Rahul ,

What i would suggest is keep the select statement as it is , then loop on your internal table where document type is CT, read the table and find if there is any record with the same document number , material and document type CT1 , if it is there then delete the record from internal table.

the code will look some thing like this

LOOP AT it_ZEXC_REC  WHERE RDOCTYP = 'CT'.
READ TABLE it_ZEXC_REC INTO WA_1 
WITH KEY DOCNO   =  it_ZEXC_RECDOCNO 
                 RDOCTYP = 'CT1'
                 MATNR = it_ZEXC_REC-MATNR.
IF SY-SUBRC = 0.
DELETE TABLE it_ZEXC_REC  INDEX SY-TABIX.
CLEAR WA_1
ENDIF.
ENDLOOP.

Regards

Arun