on change query

[Former Member]

Hi all,

loop at itAB.

ON CHANGE OF ITAB-ID.

clear : NEWNO ,id.

call function 'CONVERSION_EXIT_ALPHA_INPUT'

exporting

input = ITAB-ID

importing

output = ID.

select single max( NO ) from zXXX into NEWNO

where id = id AND WERKS = ITAB-werks.

if sy-subrc eq 0.

NEWNO = NEWNO + 1.

else.

NEWNO = 1.

endif.

ENDON.

The select statement is returning a value of 0 even when the record is not found.

I tried deleting all the records in ztable and then also it returns a value of 0.

Thanks

PREETI

[Former Member]

Hi all,

loop at itAB.

ON CHANGE OF ITAB-ID.

clear : NEWNO ,id.

call function 'CONVERSION_EXIT_ALPHA_INPUT'

exporting

input = ITAB-ID

importing

output = ID.

select single max( NO ) from zXXX into NEWNO

where id = id AND WERKS = ITAB-werks.

if sy-subrc eq 0.

NEWNO = NEWNO + 1.

else.

NEWNO = 1.

endif.

ENDON.

The select statement is returning a value of 0 even when the record is not found.

I tried deleting all the records in ztable and then also it returns a value of 0.

Thanks

PREETI

[Clemenss]

Hi preeti,

are you sure about your code?

Using Select single with aggregate function MAX can't work properly.

The select statement will return SY-SUBRC = 4 if no record with ID = ID is found.

So I don't understand your statement 'statement is returning a value of 0' in context of the given source: If SY-SUBRC = 0 and NEWNO = 0 then you say NEWNO = NEWNO + 1 leading to a value of 1 for NEWNO.

If sy-subrc <> 0 the result will be exactly the same.

I think, everything is fine.

Regards,

Clemens.

[Former Member]

Hi,

I KNOW THE VALUE OF NEWNO IS CORRECT IN EITHER CASE BECAUSE

IAM CLEARING NEWNO BEFORE SELECT QUERY

if sy-subrc eq 0.

NEWNO = NEWNO + 1.

else.

NEWNO = 1.

endif.

Using Select single with aggregate function MAX can't work properly. SO WHAT IS THE BEST WAY TO RETRIEVE MAX VALUE FROM DATA BASE TABLE.

THE SELECT STATEMENT IS NOT RETURNING A SY-SUBRC EQ 4 WHEN THE RECORD IS NOT FOUND.

I TRIED EVEN DELETING ALL RECORDS IN THE TABLE.

check eith the code

thanks

[Clemenss]

Hi preeti,

I got it, you are right about the erroneous behavior of the SELECT statement: I could not find anything about that in documentation. But you can circumvent that easily:

data: try_NEWNO like NEWNO.
* ...
select  NEWNO from zXXX into try_NEWNO
where id = id AND WERKS = ITAB-werks.
  check try_NEWNO > NEWNO.
  NEWNO = try_NEWNO.
endselect.

No you got both: A SY-SUBRC = 4 if no record exists and the maximum NEWNO if at least one records exists.

And don't worry about performance. SELECT .. ENDSELECT will retrieve blocks of max 32KB from database. In ABAP this is a fast loop and usually ABAP is faster than database processing.

Regards,

Clemens

P.S.: Thank you - I did not know and do not understand that SY-SUBRC is always zero when retrieving MAX value - special kind of logic!

[Former Member]

Hi

On Change is obsolute now.

The same functinaliy can be achieved with 'ON NEW'.

Regards

Chandrasekar

[Clemenss]

Chandrasekar,

it is called AT NEW and is available only within LOOP. And then you must make sure that it is the leftmost field with changing contents in a sorted table.

But, in a way, you are right, at least in object-oriented environment:

ON CHANGE OF - ENDON not Permitted

The pseudo control structure ON CHANGE OF - ENDON is not permitted in ABAP Objects.

ABAP Objects error message at:

ON CHANGE OF f.

...

ENDON.

Correct syntax:

DATA g LIKE f.

IF f <> g.

...

g = f.

ENDIF.

Reason:

The system internally creates a global invisible work field which cannot be controlled by the program. You are recommended to declare your own work field and process it with the IF control structure.

Regards,

Clemens

[Former Member]

Yeah its a typo error

Its 'AT NEW'...

and exactly its avilable in loop...

Check the latest SAP ABAP documentation.. its says clearly 'ON Change' is absolute..

[Clemenss]

Where did you find that?

Sometimes they declare something as 'OBSOLETE' not absolute (just another typo.

ON CHANGE OF is not declared OBSOLETE but not permitted in OO context (within METHOD) and it is recommended not to use it. But if you do anyway in non-OO-context, there is no reason why not.

Regards,

Clemens

[Former Member]

Clemens - according to <a href="https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/5ac31178-0701-0010-469a-b4d7fa2721ca">Obsolete ABAP Language Constructs</a> by Marilyn Pratt, it's obsolete.

Rob

[Former Member]

This is similar to a question posed about a week ago. I think the solution was something like:

LOOP AT itab.

  ON CHANGE OF itab-id.

    CLEAR : newno ,id.

    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
      EXPORTING
        input  = itab-id
      IMPORTING
        output = id.

    SELECT no
      FROM zxxx
      UP TO 1 ROWS
      INTO newno
      WHERE id = id
        AND werks = itab-werks
      ORDER BY no DESCENDING.
    ENDSELECT.

    IF sy-subrc EQ 0.
      newno = newno + 1.
    ELSE.
      newno = 1.

    ENDIF.
  ENDON.
ENDLOOP.

Rob