selecting single record from Table

[anuja_s]

Hi Experts

My code is as below:

IF S_COUNTER_DATAPAKID = 0.

OPEN CUSRSOR WITH HOLD S_CURSOR FOR

SELECT OBJID AEDTM FROM HRP1000

          WHERE PLVAR = 'A1' AND OTYPE = 'S' AND ENDDA = '99991231'.


    ENDIF.       

here, i am getting more than one AEDTM for 1 OBJID. But, i want only latest date(AEDTM) , how to change this code. I am adding sort statement but it is not allowing to add SORT before SELECT.

Please guide.

Thanks in advance.

[Former Member]

Hi Anuja ;

Please check the HRP1000-LANGU field, maybe this field can filter the result.

Regards

Özgün

[former_member194739]

Dear Anuja,

Use select single in SELECT statement with where conditions.

Regards,

Abbas.

[anuja_s]

Hi

I can not use SELECT SINGLE and MAX, as it is not allowing to use inside CURSOR.

[former_member302911]

You not need to use SELECT MAX inside CURSOR but use SUB Query, is different.

In your case the code becomes:

         OPEN CURSOR WITH HOLD S_CURSOR FOR

         SELECT OBJID AEDTM FROM HRP1000
          WHERE PLVAR = 'A1' AND OTYPE = 'S' AND ENDDA = '99991231'

          AND AEDTM = ( SELECT MAX( AEDTM ) FROM HRP1000 WHERE

          PLVAR = 'A1' AND OTYPE = 'S' AND ENDDA = '99991231' ).

Regards,

Angelo.

[former_member302911]

In Where condition try to use SUB Query:

... AND AEDTM = ( SELECT MAX( AEDTM ) FROM HRP1000 WHERE ... ).

Regards,

Angelo.

[Former Member]

Select the max date with single statement

[Former Member]

With subquery of course