SQL oracle - how to get max from column, add one ,format it and concat with more values

user_7414389 0
Viewed 135

I'm currently trying to make a Query which have to get the max value from a column, add one (+1) and format it with N zeros in the left side until nine positions.

I have tried a lot of queries which works in H2 database but don't do the same in Oracle

INSERT INTO XXX.YYY(X,Y,Z) 
VALUES 
(CONCAT(:X, :Y, (LPAD((SELECT nvl(max(Z),0) + 1 FROM XXX.YYY),9,'0'))), 
(LPAD((SELECT nvl(max(Z),0) + 1 FROM XXX.YYY),9,'0')),
 :Z)

This is throwing me the following error:

java.sql.SQLSyntaxErrorException: ORA-00909: invalid number of arguments

h2 oracle sql
1 Answers

There is a problem with this line.

(CONCAT(:X, :Y, (LPAD((SELECT nvl(max(Z),0) + 1 FROM XXX.YYY),9,'0'))),

Oracle does not allow more than 2 arguments. use pipe instead as Concat operator (||)

(:X || :Y || (LPAD((SELECT nvl(max(Z),0) + 1 FROM XXX.YYY),9,'0')),

Also, you can make your query better with the INSERT INTO .. SELECT as follows:

INSERT INTO XXX.YYY(X,Y,Z) 
select :X || :Y || (LPAD(nvl(max(Z),0) + 1 ,9,'0')),
(LPAD(nvl(max(Z),0) + 1 ,9,'0')),
:Z from XXX.YYY;
user_115656290
Related