topic Re: Select MAX query problem . in Application Development and Automation Discussions

Select MAX query problem .

Former Member — Tue, 04 Sep 2012 08:57:29 GMT

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

Re: Select MAX query problem .

former_member212854 — Tue, 04 Sep 2012 09:03:54 GMT

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.

Re: Select MAX query problem .

Former Member — Tue, 04 Sep 2012 09:10:18 GMT

HI Ashwin ,

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

So how can I identify the issue ?

Thanks,

Supriya

Re: Select MAX query problem .

former_member212854 — Tue, 04 Sep 2012 09:17:23 GMT

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.

Re: Select MAX query problem .

former_member212854 — Tue, 04 Sep 2012 09:20:20 GMT

Hi Supriya,

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

regards,

Ashwin.

Re: Select MAX query problem .

Former Member — Tue, 04 Sep 2012 09:33:29 GMT

Hi Ashwin ,

Tried all the options , but still same error .

Regards,

Supriya

Re: Select MAX query problem .

Former Member — Tue, 04 Sep 2012 09:45:37 GMT

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.

Re: Select MAX query problem .

former_member212854 — Tue, 04 Sep 2012 09:56:15 GMT

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.

Re: Select MAX query problem .

former_member212854 — Tue, 04 Sep 2012 10:05:42 GMT

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

Re: Select MAX query problem .

former_member212854 — Tue, 04 Sep 2012 10:15:26 GMT

Hi Supriya,

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

SELECT SINGLE ....

regards,

Ashwin