Select the maximum column value in a table without rows

Question

I am using oracle database

When inserting a row into a table, I need to find the maximum value of the column and increase it by 1 and use this value in the row that 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 they have at least one entry in the table. But returns null when they are not records in the table.

How can I get the default value of 1 if they are not in the table.

Answer 1

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

Answer 2

This is not a safe way to create an auto-zoom field. You can use the Oracle sequence to achieve this.

, NVL (, 0), null.

Answer 3

. . .

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

Answer 4

:

INSERT INTO dts_route 
   (ROUTE_ID)
SELECT COALESCE(MAX(r.route_id), 0) +1
  FROM ROUTE r

... sequence :

CREATE SEQUENCE dts_route_seq;

...

INSERT INTO dts_route 
   (ROUTE_ID)
SELECT dts_route_seq.NEXTVAL
  FROM DUAL;

Answer 5

NULL

SELECT NVL(MAX(ROUTE_ID),0)

,

Answer 6

, NOCACHE:

CREATE SEQUENCE dts_route_seq NOCACHE;

Note that there is performance because Oracle should now “commit” every time you increase the sequence.