I have a table that has a primary key consisting of 2 columns. One is
a release name. The other is a number which refers to an iteration
within that release.
Such as
current 1
current 2
soon 1
the release name refers to a release table which contains nothing but
the name. A release does not have to have spawned any iterations at
any given point in time.
My problem is that I need to find out the next iteration number for a
given release when creating a new iteration. In the above example the
number for current should be 3 and the number for 'soon' would be 2.
No problem so far. Within my Stored Procedure to add iterations I
simply look up the max iterationNumber. The problem arises if a
release does not yet have any iterations. Performing the search
select max(iteration_number) from iteration where release_name = blah
suddenly throws a NoDataFoundException. All I want it to do is return
1, so I can create an iteration row. Is there any way I can establish
a default result that will return if no date is found?
I would like to avoid using sequencies for each release because the
sequencies would be very brief and they seem complicated to set up at
run time.
I'm sure this problem is surmountable, I'm just not sure where to look
for the answer...