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