Hi All,

I have a requirement in which there is one incremental field based on city.


Based on city the SR No needs to be incremented. We have achieved this by using Rank function and its working fine.



Now the problem is if we run this procedure for the next time the SR No to be incremented.

For Eg: If we run the procedure for the 1’st following is the output.

SR No City
1 Chennai

1 Chennai

2 Mumbai

3 Hyderabad

3 Hyderabad

If we run the procedure for 2’nd time the output should be as given below.



SR No City
4 Chennai

4 Chennai

4 Chennai

5 Hyderabad

6 Yamuna nagar

6 Yamuna nagar



If we run the procedure for the next time the SR No should start from 7.



Please help

>> If we run the procedure for the next time the SR No should start from 7.

Well, you will have to "remember" that the SQL is being re-run . . .

You might store a flag in a PL/SQL variable, maybe use a sequence, who knows?

I have a incremental field in a procedure, which needs to be incremented for each time when we execute the procedure.

for example: if you run the procedure for the first time

SRNO
1
2
3
4

if you run the procedure for the second time

SRNO
5
6
7
8

Agree, use a sequence or store your SRNO in a temporary table so each time you run the procedure, get max SRNO+1 from temporary table right at the beginning before you do any processing.

we need to implement this with in the same procedure with out sequence.
It is not depends on the user ,it depends on the procedure.
each time it is running the SRNO should be increment based on previous SRNO.

>> we need to implement this with in the same procedure with out sequence.

You didn't say that before.

If you are not allowed to do it the right way, I would quit and find a better job . . .


**************************************************
>> each time it is running the SRNO should be increment based on previous SRNO.

Sounds easy, programming 101, what's the problem?

You don't know how to increment a counter in PL/SQL?



There you go, problem solved . . .