select max value of a column in table with no rows

Question

I am using oracle database

While inserting a row in a table, i need to find the max value of a column and increment it by 1, and use that value in row i am inserting.

INSERT INTO dts_route 
   (ROUTE_ID, ROUTE_UID, ROUTE_FOLDER)
VALUES (
                        (SELECT MAX(ROUTE_ID) + 1 FROM  route) ,
                        ROUTE_UID,
                        ROUTE_FOLDER)

This works fine if their is at least one entry in table. But returns null when their are no entries in table.

How can i get default value of 1 when their are no entries in table.

Answer 1

SELECT COALESCE(MAX(ROUTE_ID),0) ...

Answer 2

This is not a safe way of creating an auto-increment field. You can use an Oracle sequence to achieve this goal.

As for the null, you can use NVL to give a default value (say, 0) in case the function returns null.

Answer 3

Use sequence for the ID. You need to create sequence. See below link

http://www.basis.com/onlinedocs/documentation/b3odbc/sql_sequences.htm