topic Re: MIN in select SINGLE query. in Application Development and Automation Discussions

MIN in select SINGLE query.

Former Member — Tue, 23 Oct 2007 08:35:55 GMT

I need to find a single record with lowest DDUP.

If there are 2 records exist, then i need a single record with lowest DDUP.

loop at it_mara.

***

SELECT SINGLE EKORG MATNR MFRPN ZEINR KUNNRS MIN( DDUP ) DCURR

FROM ZNVT_PRICE

INTO CORRESPONDING FIELDS OF LW_ZNVT_PRICE

WHERE EKORG EQ P_EKORG AND

MATNR EQ IT_MARA-MATNR AND

MFRPN EQ IT_MARA-MFRPN AND

ZEINR EQ IT_MARA-ZEINR AND

KUNNRS EQ IT_MARA-KUNNR AND

DATAB LE SY-DATLO AND

DATBI GE SY-DATLO

GROUP BY EKORG MATNR MFRPN ZEINR KUNNRS DCURR.

****

***

endloop.

there is only one record in the table ZNVT_PRICE. (1234.00)

Some times i am getting 1234.

Some times 0.00.

I need to find a single record with lowest DDUP.

If there are 2 records exist, then i need a single record with lowest DDUP.

if I do not use GROUP BY i am getting error.

I have to use SELECT SINGLE.

Guys...please help me to write reliable query.

Former Member — Tue, 23 Oct 2007 08:40:58 GMT

Hi,

Instead of SELECT SINGLE use

SELECT.. UP TO 1 ROWS

ENDSELECT.

It will also be very fast and suit your requirement.

Regards,

Atish

Former Member — Tue, 23 Oct 2007 08:43:42 GMT

instead to select single in the loop

select all the records outside the loop

then sort your internal table in decending order with DDUP

then read the first record

now u will have the lowest ddup