topic Query urgent in Application Development and Automation Discussions

Query urgent

Former Member — Fri, 06 Jul 2007 06:05:43 GMT

Hi all,

Plz go through this query, I am using two tables in this select query, so I am using inner join.

But in this query it repeats data.

two tables eban and ebkn.

equating from eban some of the fields ernam badat and so.

If see the records in se11 eban tables

user : thangesh

badat : 01-07-2007 to 06-07-2007 means it showing 19 records,

But in my RFC function module if run it is showing 29 records,

Plz help me.

SELECT A_{BADAT A}BANFN A_{BNFPO A}LFDAT A_{TXZ01 A}MENGE A_{MEINS A}BANPR B~KOSTL

INTO CORRESPONDING FIELDS OF TABLE T_ZPR_REQ FROM ( EBAN AS A INNER JOIN EBKN AS B

ON A_{BANFN = B}BANFN ) WHERE A_{ERNAM = LOGONN AND A}BADAT BETWEEN A1 AND SY-DATUM

AND A_{BSART = 'NB' AND A}BSTYP = 'B' AND A~EKGRP = '001'.

Re: Query urgent

Former Member — Fri, 06 Jul 2007 06:09:27 GMT

Hi Vijay

Are the values repeating or the values all are unique. Check it ones. If the values are repeating then apply the condiiton

Delete all adjacent entries by comparing itab1-badat

or u can use refresh statement

Reward all helpfull answers

Regards

Pavan

Re: Query urgent

Former Member — Fri, 06 Jul 2007 06:11:39 GMT

Your query is correct, may be there are multiple records,

Check the records and if there are multiple records, use

DELETE ADJACENT DUPLICATES FROM TABLE COMPARING <FIELD>

Regards,

Pavan P.

Re: Query urgent

Former Member — Fri, 06 Jul 2007 06:22:38 GMT

Hi,

use the following statement...

Syntax:

DELETE ADJACENT DUPLICATES FROM TABLE COMPARING <FIELD>

E.g.

DELETE ADJACENT DUPLICATES FROM TABLE COMPARING ITAB-BADAT.

if it helps you reward points...

Reg,

<b>Suresh. V</b>

Re: Query urgent

Former Member — Fri, 06 Jul 2007 07:14:22 GMT

Hi praveen,

There are duplicate values,

Where should i use refresh statement after my select statement only know.

plz help me out.

SELECT A_{BADAT A}BANFN A_{BNFPO A}LFDAT A_{TXZ01 A}MENGE A_{MEINS A}BANPR B~KOSTL

INTO CORRESPONDING FIELDS OF TABLE T_ZPR_REQ FROM ( EBAN AS A INNER JOIN EBKN AS B

ON A_{BANFN = B}BANFN ) WHERE A_{ERNAM = LOGONN AND A}BADAT BETWEEN A1 AND SY-DATUM

AND A_{BSART = 'NB' AND A}BSTYP = 'B' AND A~EKGRP = '001'.

*REFRESH T_ZPR_REQ.

*REFRESH T_ZPR_REQ FROM TABLE EBAN.

*Delete all adjacent entries by comparing itab1-badat.

DELETE ADJACENT DUPLICATES FROM T_ZPR_REQ COMPARING ALL FIELDS.

Re: Query urgent

Former Member — Fri, 06 Jul 2007 07:27:53 GMT

If there are duplicates then it is best to go through this statement

SELECT A~BADAT A~BANFN A~BNFPO A~LFDAT A~TXZ01 A~MENGE A~MEINS A~BANPR B~KOSTL
INTO CORRESPONDING FIELDS OF TABLE T_ZPR_REQ FROM ( EBAN AS A INNER JOIN EBKN AS B
ON A~BANFN = B~BANFN ) WHERE A~ERNAM = LOGONN AND A~BADAT BETWEEN A1 AND SY-DATUM
AND A~BSART = 'NB' AND A~BSTYP = 'B' AND A~EKGRP = '001'.
DELETE ADJACENT DUPLICATES FROM itab. <-- your internal table

Check this sample code for using a clear statement

DATA: BEGIN OF itab OCCURS 0,

        EBELN LIKE EKKO-EBELN,
        KNUMV LIKE EKKO-KNUMV,
        LIFNR LIKE EKKO-LIFNR,
        EBELP LIKE KONV-KPOSN,
        NETWR LIKE EKPO-NETWR,
        NETPR LIKE EKPO-NETPR,
        NAME1 LIKE LFA1-NAME1,
        NAME2 LIKE LFA1-NAME2,
        
     END OF itab.
SELECT A~EBELN A~LIFNR A~KNUMV B~EBELP B~NETWR B~NETPR B~WERKS B~MATNR
        L~NAME1 L~NAME2
    FROM EKKO  AS A
       INNER JOIN  EKPO AS B ON A~EBELN = B~EBELN
       INNER JOIN  LFA1 AS L ON L~LIFNR = A~LIFNR
*       INNER JOIN  EKKN AS C ON C~EBELN = A~EBELN

       INTO CORRESPONDING FIELDS OF TABLE itab
       WHERE B~BUKRS = 'company code'       .

sort itab by ebeln ebelp.

IF ITAB3-KSCHL = 'NAVS' OR ITAB3-KSCHL = 'NAVM'.

            ITAB3-KBETR = ITAB1-KBETR.

            modify  ITAB3 transporting KBETR
            where  KNUMV =  ITAB3-KNUMV AND EBELP = ITAB3-EBELP .
            clear: itab1-kbetr.

check this for using delete adjacent duplicates

TYPES: BEGIN OF NAMETAB_TYPE, 
         NAME(30) TYPE C, 
       END OF NAMETAB_TYPE. 

DATA: NAMETAB TYPE STANDARD TABLE OF NAMETAB_TYPE WITH 
                   NON-UNIQUE DEFAULT KEY INITIAL SIZE 100. 
... 
DELETE NAMETAB FROM 5 TO 36 WHERE NAME CA 'ABC'.

DELETE ADJACENT DUPLICATES FROM itab.

or you can extend this for all entries also as

 comparing all fields

Reward all helpfull asnwers

Regards

Pavan

Re: Query urgent

kesavadas_thekkillath — Fri, 06 Jul 2007 11:20:26 GMT

check if there is any other key field to be compared in "on" statement.....