Select MAX query problem .

[Former Member]

Hi all ,

I need to get the maximum date of an action run for a particular employee , hence I am using MAX keyword as follows

   SELECT MAX( begda ) FROM pa0000 INTO g_begda WHERE pernr EQ i_pernr
                                               AND   massn EQ c_zb .

here even if i do not have en employee which satisfies the above condition , still my sy-subrc is coming 0 and i am getting 0000000 date in g_begda .

Can you pl let me know what could be the issue ?

Is it something to do with MAX keyword ?

Thanks,

Supriya

[former_member212854]

Hi Supriya,

with sy-subrc also check sy-dbcnt(number of database records processed ) after the select.

If sy-dbcnt is eq '0' it means your database table consists of a record with empty pernr.

regards,

Ashwin.

[Former Member]

HI Ashwin ,

Even sy-dbcnt is 1 after i execute the above query .

So how can I identify the issue ?

Thanks,

Supriya

[former_member212854]

Hi Supriya,

check in the database if any entry exist with pernr eq space

also add distinct before the pernr like MAX( DISTINCT begda )

regards,

Ashwin.

[former_member212854]

Hi Supriya,

Also try adding 'Group BY pernr' in the select statmement.

regards,

Ashwin.

[Former Member]

Hi Ashwin ,

Tried all the options , but still same error .

Regards,

Supriya

[former_member212854]

Hi Supriya,

Did you check in the table whether you have empty pernr ?

If you have empty pernr, then it means that the output what you are getting is right.

If your I_pernr is always empty then

try this ..

clear I_pernr.

   SELECT MAX( begda ) FROM pa0000 INTO g_begda WHERE pernr NE i_pernr.

you will get a value.

[former_member212854]

Hi Supriya,

Could you throw some more light on your issue ?

As you have said you are facing the problem when you dont pass the pernr, then instead of checking for sy-subrc and do the further processing why dont you simply check IF g_begda is initial or not and do the further processing ?

do you want to get the MAX of BEGDA for where condition in MASSN ? and not for perner ? Please aloborate your issue ...

regards,

Ashwin

[former_member212854]

Hi Supriya,

And also not to forget to add SINGLE after the select.

SELECT SINGLE ....

regards,

Ashwin

[Former Member]

Hi,

using MAX will give you a default aggregation i.e aggregation on 0 selected row.

Instead use select as below.

SELECT begda

     UP TO 1 ROWS

     FROM pa0000

     WHERE pernr EQ i_pernr

     AND      massn EQ c_zb

     ORDER BY begda DESCENDING.

ENDSELECT.