Using SELECT statement in an INSERT statement in HANA

[yash_rastogi31]

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

[former_member184768 · Accepted solution]

Assuming that the column names are in mixed case, please try the following. If your column names are not in mixed case, kindly specify the the column names in correct case:

INSERT INTO CUSTOM_TABLE

("Code", "Name","OBJECTID" ,"DOCNUM", "REFNUM")

SELECT (IFNULL(MAX(CAST("Code" AS INT)), 0) + 1), (IFNULL(MAX(CAST("Code" AS INT)), 0) + 1), '1', '1', '1'

FROM CUSTOM_TABLE;

Regards,

Ravi

[yash_rastogi31 · Accepted solution]

Hi Ravi,

Thanks a lot. It worked successfully. The only question I'm left as of now is of NOLOCK issue.

Regards,

Yash