CONCAT函数中超过2列 [英] More than 2 columns in a CONCAT function

问题描述

在SQL Server 2012中，我希望将 concat 5列转换为1列，但是在查询中它可以工作，但是当我放入视图时，它给我类似

In SQL Server 2012 I want to concat 5 columns into 1 but in the query it works but when I put in in a view it gives me an error like

消息174，级别15，状态1，第3行

CONCAT函数需要2个参数。

Msg 174, Level 15, State 1, Line 3
The CONCAT function requires 2 argument(s).

有什么问题，所以我可以解决，因为 concat 是可连接多于一列，因为如果为空，则将其设为空。.

What's the problem so I can fix it because concat is a good function for concatenate more than 1 column because if its null they make it empty..

CODE：

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)     

推荐答案

您认为其他地方一定有错误！

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.'

为此

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

编辑：

仅解释代码的不同之处在于，带有{fn ....}的代码是一种规范函数，Microsoft承诺将在所有ODBC连接。

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.

从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.

这篇关于CONCAT函数中超过2列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！