I have the following query, which normally should work, but does not work with my navicat database magagement software:
INSERT INTO TABLE1(ID_NO, COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5, COLUMN6, COLUMN7, COLUMN8, COLUMN9, COLUMN10, COLUMN11, DDATE_IN) VALUES(SELECT ID_NO+1 AS ID_NO FROM ((SELECT MAX( ID_NO ) as ID_NO FROM TABLE GROUP BY ID_NO ORDER BY ID_NO DESC) WHERE ROWNUM = 1), 'Value2', 'Value3', 1, 1, 1, 'Value4', 'Value5', 'Value6', 'Value7', 'Value8', 'Value9', TO_DATE('05.07.2020 10:00:00', 'DD.MM.YYYY HH24:MI:SS'))
The SELECT query should take the highest already existing ID_NO (ID number) of TABLE1 and add 1 and write this in the INSERT statement.So the ID_NO has to be iterated which each new INSERT in that table.
If I write the SELECT statement as a single statement in navicat without the INSERT prompt it works. But I want to put it in the INSERT prompt string. I have to have the combined query in a single string, because I use a C# code which sends the oracle query string to the database and that works only with one single string. What is wrong here? I am stuck since a few days and I can not see the mistake.
Edit: I found the mistake by myself. I forgot to put the SELECT Statement into brackets, i.e. into "()". I edited the code of my first post. Now it works.
I got it. I forgot to set the SELECT Statement into brackets, i.e. into "()". I edited my code. Now it works.