Error Concat function requires 2 arguments . Incorrect Syntax near =

Question

User-797751191 posted

Hi

  In below code i get above error

SELECT        DocEntry, { fn CONCAT(DocNum, '  -  ', CardName) } AS Expr1
FROM            dbo.test0 AS a
WHERE        ((SELECT        State
                            FROM            dbo.test1 AS x
                            WHERE        (CardCode = a.CardCode)) = 'LDH')

Thanks

Answer 1

User77042963 posted

In SQL Server,

SELECT DocEntry, CONCAT(DocNum, ' - ', CardName) AS Expr1...

Answer 2

User-719153870 posted

Hi jsshivalik,

As you provided in the subject, you met two errors when excute this query.

jsshivalik

Concat function requires 2 arguments

The ODBC Scalar Function {fn CONCAT(argument1,argument2)} requires 2 and only 2 arguments.

One of the solutions is to use the CONCAT() function directly like @limno provided.

Another solution, if you insist (or have no choice but only can) using the {fn CONCAT(argument1,argument2)}, please update the query like below:

SELECT DocEntry,{fn CONCAT({fn CONCAT(DocNum,'-')},CardName)} AS Expr1 FROM dbo.test0

As you can see, use it twice for the third argument.

jsshivalik

Incorrect Syntax near =

This error occurs because the SQL can not recognize statement like:

(SELECT        State
                            FROM            dbo.test1 AS x
                            WHERE        (CardCode = a.CardCode)) = 'LDH'

The solution for this error, use join instead of ambiguous simple select statement.

Please refer to below demo to update your query:

create table #test0(CardCode int identity(1,1),DocNum varchar(50),CardName varchar(50),DocEntry varchar(50))
create table #test1(CardCode int ,State varchar(50))

insert into #test0 values('DocNum1','CardName1','DocEntry1'),('DocNum2','CardName2','DocEntry2'),('DocNum3','CardName3','DocEntry3')

insert into #test1 values(1,'LDH'),(2,'AAA'),(3,'LDH'),(4,'LDH')

SELECT DocEntry,{fn CONCAT({fn CONCAT(DocNum,'-')},CardName)} AS Expr1 FROM #test0 a left join #test1 x on a.CardCode=x.CardCode where x.State='LDH'

And the output:

Best Regard,

Yang Shen