Skip to Content
0
Nov 19, 2013 at 08:49 AM

Using SELECT statement in an INSERT statement in HANA

11860 Views

HI,

I've a SQL query in which I retrieve an integer value from "Code" field of a CUSTOM_TABLE, and use this retrieved value in an INSERT statement in the same CUSTOM_TABLE. The SQL query is as follows:

DECLARE @MAXNO INT

SELECT @MAXNO=(ISNULL(MAX(CAST(Code AS INT)), 0) + 1) FROM CUSTOM_TABLE

INSERT INTO CUSTOM_TABLE(Code, Name,[OBJECTID] ,[DOCNUM],[REFNUM]) VALUES(@MAXNO, @MAXNO,N'1', N'1', N'1');

Now I'm getting an error (incorrect syntax near select) when I execute this query in HANA after transforming the query as follows:

INSERT INTO CUSTOM_TABLE(Code, Name,[OBJECTID] ,[DOCNUM],[REFNUM]) VALUES(SELECT (IFNULL(MAX(CAST(Code AS INT)), 0) + 1) FROM CUSTOM_TABLE, SELECT (IFNULL(MAX(CAST(Code AS INT)), 0) + 1) FROM CUSTOM_TABLE,N'1', N'1', N'1');

What is the syntax for a HANA query in which I want to use SELECT statement in an INSERT statement?

Regards,

Yash