topic Re: Appropriate select querry needed in Application Development and Automation Discussions

Appropriate select querry needed

Former Member — Mon, 02 May 2011 07:12:55 GMT

Hi Experts,

f lt_/bic/qzcdcy[] is not initial.

SELECT /bic/zcdcy MIN( act_due )

FROM /bi0/mcdcy_act

INTO TABLE lt_zdrgsrnrr

FOR ALL ENTRIES IN lt_/bic/qzcdcy

WHERE /bic/zcdcy eq lt_/bic/qzcdcy-/bic/zcdcy

AND act_type = '9055'

AND act_end NE '00000000'

AND act_stat = 1

GROUP BY /bic/zcdcy.

Endif.

I am using above query but what i got the error write bellow

The addition "FOR ALL ENTRIES" excludes all aggregate functions with

the exception of "COUNT( * )" as the single element of the SELECT clause.

Regards,

dhanu

Re: Appropriate select querry needed

Former Member — Mon, 02 May 2011 07:42:11 GMT

Hi,

replace MIN( act_due ) with act_due and you will get duplicates.

Sort your result table lt_zdrgsrnrr by /bic/zcdcy and act_due ascending and use

DELETE ADJACENT DUPLICATES FROM lt_zdrgsrnrr COMPARING /bic/zcdcy.

to get your wanted results.

Regards,

Klaus

Re: Appropriate select querry needed

Former Member — Mon, 02 May 2011 08:34:07 GMT

you can not combine aggregates with FOR ALL ENTRIES (the COUNT(*) expample is a dummy problem).

So do the SELECT and then calculate the MIN.

You don't need the DELETE ADJACENT DUPLICATES, as this is anyway done by the FAE.

Re: Appropriate select querry needed

Former Member — Mon, 02 May 2011 09:40:27 GMT

Sorry Siegfried, you're wrong, the DELETE is for calculation of the MIN() and it is necessary! Duplicates from FAE only will be deleted for duplicate combinations of both fields. Here DELETE is needed to find the one and only right combination of both fields after the SORT!

Regards,

Klaus