Need to select record from the group of records

[Former Member]

Hi

I have this statement

SELECT * FROM ENT6418 WHERE VBELN >= '0090000000'.

             MOVE: ENT6418-PERNR TO REC-VRTNR.
             PERFORM GET_INVOICE_NUMBER.
             CLEAR: REC, TEMP.
ENDSELECT.

It works fine but the problem is ENT6418 has multiple records for the same VBELN

Is there any statement that I can use so that it selects only one from the multiple records

This is what I want

0090000001--|

0090000001| select one of these

0090000001--|

0090000002<-- select this one

0090000003<-- select this one

0090000004--|

0090000004--| Select One of these

0090000005<-- Select this one

Please Help

[Former Member]

U can write ..

SELECT * up to 1 rows

FROM ENT6418 WHERE VBELN >= '0090000000'.

MOVE: ENT6418-PERNR TO REC-VRTNR.

PERFORM GET_INVOICE_NUMBER.

CLEAR: REC, TEMP.

ENDSELECT.

[Former Member]

ok now i tried this one

SELECT * up to 1 rows
              FROM ENT6418
              INTO CORRESPONDING FIELDS OF wa
              WHERE VBELN >= '0090000000'.

             MOVE: wa-PERNR TO REC-VRTNR.
             PERFORM GET_INVOICE_NUMBER.
             CLEAR: REC, TEMP.
          ENDSELECT.

But i get only one invoice number for multiple times which i don't want.

0090095162

0090095162

0090095162

0090095162

Please help why is the distinct code not working i get all the invoices but also multiple time like without the DISTINCT as the original code.

I debugged it and before it executes the select command i can see wa-VBELN has the next invoice number but once it executes the select command it goes back to the the first number?

[Former Member]

Add the DISTINCT keyword to the select statemant.

[Former Member]

how do you write the distinct on my statement? I need all the other fields too.

I tried with this

SELECT DISTINCT * FROM ENT6418 WHERE VBELN >= '0090000000'.

not working

[Former Member]

In a LOOP while processing the table.. look at the use of AT NEW OF <field>.

AT END OF f.

Effect

f is a field from the field group HEADER. The enclosed sequence of statements is executed if

the field f occurs in the sort key of the extract dataset (and thus also in the field group HEADER) and

the field f or a superior sort criterion has a different value in the current LOOP line than in the preceding (AT NEW) or subsequent (AT END OF) record of the extract dataset.

If f is not an assigned field symbol, the control break criterion is ignored, and the subsequent sequence of statements is not executed. If a field symbol is assigned, but does not point to the HEADER field group, the system triggers a runtime error.

Example

DATA: NAME(30), 
      SALES TYPE I. 
FIELD-GROUPS: HEADER, INFOS. 
INSERT: NAME  INTO HEADER, 
        SALES INTO INFOS. 
... 
LOOP. 
  AT NEW NAME. 
    NEW-PAGE. 
  ENDAT. 
  ... 
  AT END OF NAME. 
    WRITE: / NAME, SUM(SALES). 
  ENDAT. 
ENDLOOP. 

Notes

If the extract dataset is not sorted before processing with LOOP, no control level structure is defined and the statements following AT NEW or AT END OF are not executed.

Fields which stand at hex zero are ignored by the control break check with AT NEW or AT END OF. This corresponds to the behavior of the SORT statement, which always places unoccupied fields (i.e. fields which stand at hex zero) before all occupied fields when sorting extract datasets, regardless of whether the sort sequence is in ascending or descending order.

[Former Member]

hi use this,

delete adjacent duplicates form table itab comparing VBELN

then u will get always one record for one vbeln...

regards,

venkat.

[Former Member]

HI,

Refer the sample code below using distinct.

DATA: wa   TYPE spfli, 
      ftab TYPE TABLE OF STRING. 

APPEND 'CITYFROM' TO ftab. 
APPEND 'CITYTO'   TO ftab. 

SELECT DISTINCT (ftab) 
       FROM spfli 
       INTO CORRESPONDING FIELDS OF wa 
       WHERE 
         carrid   = 'LH'. 
  WRITE: / wa-cityfrom, wa-cityto. 
ENDSELECT. 

Thanks,

Sriram Ponna.

[Former Member]

Did this one but it returns always the same VBELN and not working

DATA: wa TYPE ENT6418,

ftab TYPE TABLE OF STRING.

APPEND 'VBELN' TO ftab.

SELECT DISTINCT (ftab) FROM ENT6418

INTO CORRESPONDING FIELDS OF wa

WHERE VBELN >= '0090000000'.

MOVE: wa-PERNR TO REC-VRTNR.

PERFORM GET_INVOICE_NUMBER.

CLEAR: REC, TEMP.

ENDSELECT.

ENDIF.

ENDSELECT.