generate auto increment field using ecmap

[sowmyat]

Hi,
I am using ecmap connected to oracle database. I need to create a sequence number field which starts from 1 and auto increments. I have tried declaring an ecmap variable and incrementing by 1, but this sets itself to 0 the next time the map runs and starts counting from 1 again,
i.,e I get
1
2
3
1
2
3
where as I need,
1
2
3
4
5
6
THanks

 

[BocaBurger]

I think you can do this in Oracle automatically. Ask your DBA.


BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[sowmyat]

I know we can do it in oracle, I want to know how to do it in ecmap/

 

[longhair]

sowmyat,
never used ecmap but what you logically have to do is read through your table first, find the last (highest) sequence number in the field then set you variable to that number + 1.
regards,
longhair

 

[sowmyat]

yes, I am working on that logic, I can do the rest but I do not know how to read the highest sequence number.

 

[longhair]

soqmyat,
that is dependent upon the language / keywords. but the general idea is as follows:
start with 2 variables say a and b.
set both = to 0 (zero).
get your first record. set a = to the sequence number.
if a > b then set b = to a.
move onto the next record and repeat the logic on the above line. in the end b will be the highest sequence number. on a related note if the sequence number field is indexed it will be a faster search. your development language may also have a phrase such as 'find last'.
regards,
longhair

 

[sowmyat]

I figured it out , thanks for the replies.

In Ecmap we can execute a sql statement
'UPDATE UMEMP_SXC_DRUG SET SEQNBR = ROWNUM'

This eleminates the need to create a sequence and a trigger if done on oracle database.