topic Re: Select Query Problem in Application Development and Automation Discussions

Select Query Problem

Former Member — Thu, 25 Jan 2007 06:46:23 GMT

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.

Re: Select Query Problem

Former Member — Thu, 25 Jan 2007 06:50:41 GMT

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

and doctyp in ( 'CT' , 'CT1' ).

sort table it_zexc_rec by matnr.

delete adjacent duplicates from it_zexc_rec comparing matnr.

.

Regards

Subramanian

Re: Select Query Problem

Former Member — Thu, 25 Jan 2007 06:55:50 GMT

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

Re: Select Query Problem

Former Member — Thu, 25 Jan 2007 06:56:14 GMT

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

Re: Select Query Problem

Former Member — Thu, 25 Jan 2007 06:56:20 GMT

Thanks a lot Subramanian ,

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

Re: Select Query Problem

Former Member — Thu, 25 Jan 2007 06:59:50 GMT

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

Re: Select Query Problem

Former Member — Thu, 25 Jan 2007 09:10:08 GMT

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.

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.

delete adjacent duplicates from itab comparing b.

loop at itab.

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

endloop.

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 :

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

Thanks and Regards,

Kunal