topic Re: increasing a table field in Application Development and Automation Discussions

increasing a table field

Former Member — Thu, 13 Oct 2005 16:35:12 GMT

Hello,

someone knows a way to increase a table field value by one whenever a record is inserted on the same table?.

For example:

Table: zvquery

fields: client, partner, type, count.

So whenever you insert a new record on table zvquery, field count increases automatically by 1.

Thank you very much.

Re: increasing a table field

RichHeilman — Thu, 13 Oct 2005 16:38:23 GMT

There is no such functionality in ABAP. We use number ranges. You call a function module to get the next available number and then use that number to update the database. You set up the number range in transaction SNRO. Then this function module to get the next number.

  call function 'NUMBER_GET_NEXT'
       exporting
        nr_range_nr      = '01' " This is the number range interval  
        object           = 'ZSDINQUIRY'  " This is your object
       importing
            number                  = next_number
       exceptions
            interval_not_found      = 1
            number_range_not_intern = 2
            object_not_found        = 3
            quantity_is_0           = 4
            quantity_is_not_1       = 5
            interval_overflow       = 6
            buffer_overflow         = 7
            others                  = 8.

Welcome to SDN. Please remember to award points for helpful answers and mark your posts as "Solved" if your question has been answered. Thanks.

Regards,

Rich Heilman

Re: increasing a table field

Former Member — Thu, 13 Oct 2005 16:39:28 GMT

You will have to explicitly code it when you update the table.

data count type I.

Select count( * ) into count from zvquery.

zvquery-count = count + 1 .

zvquery-partner = 'XXX'.

zvquery-type = 'XX'.

insert zvquery.

Cheers

Re: increasing a table field

Former Member — Thu, 13 Oct 2005 16:41:03 GMT

No automated way. You have to do it programmatically. You can get the count of the existing records add one to it and insert your new record with that value. You can also do it using custom number ranges(tcode SNRO) and then using the FM NUMBER_GET_NEXT.

Re: increasing a table field

RichHeilman — Thu, 13 Oct 2005 16:44:59 GMT

Sanjay, Srinivas, As two of the top contributors to this forum, I value your opinions greatly, I was wondering if you had a preference here. Do you prefer to use number ranges here or getting the next number from the db table. Is one better than the other. Your thoughts?

Regards,

Rich Heilman

Re: increasing a table field

Former Member — Thu, 13 Oct 2005 16:53:36 GMT

Hi Rich,

First of all I really doubt why a count is reqd as you can always get a count of number of records from count( * ) and moreover the count will mostly have the value which is already in the Primary index . So let the poster reply back as to what is the requirement to put a count field. Nonetheless Number range is preferred but that is when you want it to be a unique key field like PO Number / SO Number etc . Number range has its overheads unless you really want to develop a new application . This table seems to be a simple custom table for partner maintenance.

Cheers.

Re: increasing a table field

Former Member — Thu, 13 Oct 2005 16:56:15 GMT

ok, found the way with your help guys. Thanks a lot

Re: increasing a table field

Former Member — Thu, 13 Oct 2005 16:57:24 GMT

Thanks Rich, coming from top most contributor, that is more than a complement for me.

I prefer number ranges as opposed to the other approach simply because, number ranges automatically provides you integrity of the number assigned. By that what I mean is, if we program to read the count of the records and increment it, then there are chances that if more than one person executing this program or entering records into it, will get the same numbers. This will not be the case with number range because the moment you call the function module, that number will be reserved and will not be available to another call to the number range object. That avoids the duplicate number assignment.

Also if the number of records in the table increases, then getting a count(*) is a performance issue, compared to a NUMBER_GET_NEXT.

Srinivas