More than 2 columns in a CONCAT function

Hy. If you want to use CONCAT as a canonical function {fn CONCAT(...)} into view designer, there is a work-around solution in order to concatenate more than two columns/chars.

You can use CONCAT inside CONCAT like this:

Let's suppose you want to concatenate two codes into a single one with a "-" between

column1 = 123456

column2 = 0001

{fn CONCAT({fn CONCAT(column1, '-')}, column2)}

As a result you will have: 123456-0001

There must be an error somewhere else in your view!!

Ok, then what I did with your code was to change this line

{ fn CONCAT('T.a.v. ', C.Salutation + ' ', C.FirstName + ' ', C.MiddleName + ' ', C.LastName) } AS 'T.a.v.'

to this

CONCAT('T.a.v. ', C.Salutation + ' ', C.FirstName + ' ', C.MiddleName + ' ', C.LastName) AS 'T.a.v.'

Edit:

Just to explain the difference in code, is that the one with { fn ....} is a Canonical function and microsoft promise that it will work on all ODBC connections.

From MSDN:

Canonical functions are functions that are supported by all data providers, and can be used by all querying technologies. Canonical functions cannot be extended by a provider.

I had the same problem and all the other answers did not work for me and i did not want to use the one from Andrei.

I managed to create the view using a create view statement.

CREATE VIEW ViewName AS
SELECT        
   'Aan ' + A.Name AS 'Naam', 
   { fn CONCAT('T.a.v. ', C.Salutation + ' ', C.FirstName + ' ', C.MiddleName + ' ', C.LastName) } AS 'T.a.v.',   
   ISNULL(ISNULL(A.Address1_Line2, A.Address1_Line1), 
   C.Address1_Line2) AS 'Adres', 
   ISNULL(A.Address1_PostalCode + ' ' + A.Address1_City,     A.Address2_PostalCode + ' ' + A.Address2_City) AS 'Woonplaats',
   'heer' + ' ' + ISNULL(C.MiddleName + ' ', N'') + ISNULL(C.LastName, N'') AS 'Aanhef'  
FROM dbo.Account AS A 
FULL OUTER JOIN  
    dbo.Contact AS C ON A.Name = C.AccountIdName  
WHERE 
    (C.Salutation = 'Dhr.') AND (A.Name IS NOT NULL) AND (A.StatusCode = 1) 
    AND (ISNULL(C.StatusCode, 1) = 1) OR (C.Salutation = 'dhr.') AND (A.Name IS NOT NULL) AND (A.StatusCode = 1) AND (ISNULL(C.StatusCode, 1) = 1)