topic Re: selecting single record from Table in Application Development and Automation Discussions

selecting single record from Table

anuja_s — Thu, 15 May 2014 12:53:51 GMT

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.

Re: selecting single record from Table

Former Member — Thu, 15 May 2014 13:06:01 GMT

Hi Anuja ;

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

Regards

Özgün

Re: selecting single record from Table

former_member194739 — Thu, 15 May 2014 13:10:23 GMT

Dear Anuja,

Use select single in SELECT statement with where conditions.

Regards,

Abbas.

Re: selecting single record from Table

former_member302911 — Thu, 15 May 2014 13:19:15 GMT

In Where condition try to use SUB Query:

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

Regards,

Angelo.

Re: selecting single record from Table

anuja_s — Thu, 15 May 2014 13:32:25 GMT

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

Re: selecting single record from Table

former_member302911 — Thu, 15 May 2014 13:41:53 GMT

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.

Re: selecting single record from Table

Former Member — Thu, 15 May 2014 15:17:07 GMT

Select the max date with single statement

Re: selecting single record from Table

Former Member — Thu, 15 May 2014 15:18:56 GMT

With subquery of course