SQLTeam.com | Weblogs | Forums

Concat error in cosmos db

I am using this statement and getting error - do not know what is the issue. can some one help

CONCAT(M.MSPIPK,(CONCAT('-',M.LNKIPK,(CONCAT(M.LKFLCD)))) AS MPID,

Vendor Code: -170
Message: [SQL0170] Number of arguments for function CONCAT not valid. Cause . . . . . : A built-in function has been specified with an incorrect number of arguments. If this function is meant to be a user defined function reference, a function with this signature was not found using the current path. -- The CURDATE, CURTIME, DATABASE, GENERATE_UNIQUE, IDENTITY_VAL_LOCAL, NOW, and PI functions must be specified without any arguments. -- The ADD_MONTHS, CONCAT, IFNULL, LEFT, MOD, MULTIPLY_ALT, NEXT_DAY, NULLIF, POSSTR, POWER, RAISE_ERROR, REPEAT, RIGHT, ROUND, TIMESTAMP_FORMAT, TRUNCATE, and VARCHAR_FORMAT functions must have two arguments. -- The REPLACE function must have three arguments. -- The INSERT function must have four arguments. -- The RAND function may have zero or one argument. -- The BINARY, BLOB, CHAR, and TIMESTAMP functions may have one or two arguments. -- The CLOB, DBCLOB, GRAPHIC, STRIP, VARCHAR, VARGRAPHIC, and the encryption and decryption functions may have between one and three arguments. -- The DECIMAL, TRANSLATE, and ZONED functions may have between one and four arguments. -- The COALESCE, MAX, and MIN functions must have at least two arguments. -- The LOCATE and SUBSTRING functions may have two or three arguments. Recovery . . . : Correct the number of arguments specified for the function. If this is a user defined function, correct the path or the function signature. Try the request again.

at least provide some sample data for

MSPIPK
LNKIPK
LKFLCD

CONCAT(M.MSPIPK,(CONCAT('-',M.LNKIPK,(CONCAT(M.LKFLCD)))) AS MPID

declare @samplecosmos(MSPIPK int, LNKIPK int, LKFLCD int)

insert into @samplecosmos
select 1, 2, 3

and post the final result you want to see from these data points

1 Like

I've never used Cosmos DB or CONCAT, but just by reading the error message, I see that it says CONCAT must have two arguments. The innermost CONCAT in your code, CONCAT(M.LKFLCD), has one, and the next outermost one, CONCAT('-',M.LNKIPK,(CONCAT(M.LKFLCD))), has three. Hope this helps. And putting a space after each comma would help readability.

1 Like

Thank you, I changed this CONCAT and using PIPE now,