错误消息：Concat函数需要2个参数 [英] Error Message: Concat function requires 2 arguments
本文介绍了错误消息：Concat函数需要2个参数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下代码，当以查询方式运行时可以正常工作，但以视图方式运行时返回错误消息。我已附上错误信息。我想我在某个地方缺少支撑。
I have the following code which works fine when run as a Query but returns error message when run as view. I have attached the error message. I think I am missing a brace somewhere. If anyone can help it is much appreciated.
SELECT CASE
WHEN a.Sex = 1
THEN 'M'
WHEN a.Sex = 2
THEN 'F'
END AS Derived_Sex,
CASE
WHEN concat(b.Complete, a.[CALCULATED_AGE]) LIKE 'A%'
THEN 'Less than 1'
WHEN concat(b.Complete, a.[CALCULATED_AGE]) LIKE 'B%'
THEN 'Less than 1'
WHEN concat(b.Complete, a.[CALCULATED_AGE]) LIKE 'C%'
THEN 'Less than 1'
ELSE '1 year and above'
END AS Derived_InfantCat,
YEAR(a.DATE_OF_DEATH) AS [Derived_Year of Death],
FORMAT(a.DATE_OF_DEATH, 'MM') AS [Derived_Month of Death],
YEAR(a.DATE_OF_REGISTRATION) AS [Derived_Year of Registration],
FORMAT(a.DATE_OF_REGISTRATION, 'MM') AS [Derived_Month of Registration],
CONCAT(LEFT(CONVERT(nvarchar, a.DATE_OF_DEATH, 21), 4),
SUBSTRING(CONVERT(nvarchar, a.DATE_OF_DEATH, 21), 6, 2)) AS [Derived_YearMonth of Death],
CONCAT(LEFT(CONVERT(nvarchar, a.DATE_OF_REGISTRATION, 21), 4),
SUBSTRING(CONVERT(nvarchar, a.DATE_OF_REGISTRATION, 21), 6, 2)) AS [Derived_YearMonth of Registration],
CASE
WHEN LEN(
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END) = 3
THEN CONCAT(
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END, 'X')
ELSE
(CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END)
END AS [Derived_Cause of Death Code],
c.[4 character DESCRIPTION],
c.[3 character Sub-Chapter Description],
d.[District_Name] AS [LA NAME],
e.[WD14NM] AS [WARD NAME],
(
CASE
WHEN a.[NHS_ESTABLISHMENT_IND] = '1'
THEN 'NHS'
WHEN a.[NHS_ESTABLISHMENT_IND] = '2'
THEN 'NON-NHS'
ELSE 'OTHER'
END) AS [NHS ESTABLISHMENT TYPE DESCRIPTION],
(
CASE
WHEN a.[NHS_ESTABLISHMENT_IND] = '1'
THEN 'NHS'
WHEN a.[NHS_ESTABLISHMENT_IND] = '2'
THEN 'NON-NHS'
ELSE 'OTHER'
END) AS [NHS ESTABLISHMENT TYPE DESCRIPTION],
(
CASE
WHEN a.[NHS_ESTABLISHMENT_IND] = '1'
THEN 'NHS'
WHEN a.[NHS_ESTABLISHMENT_IND] = '2'
THEN 'NON-NHS'
ELSE 'OTHER'
END) AS [NHS ESTABLISHMENT TYPE DESCRIPTION],
(
CASE
WHEN a.PLACE_OF_DEATH_CODE = 'H'
THEN 'HOME'
WHEN a.PLACE_OF_DEATH_CODE = 'E'
THEN 'ELSEWHERE'
ELSE 'OTHER'
END
) AS [PLACE OF DEATH CODE],
concat(a.[NHS_ESTABLISHMENT_IND],'-',a.PLACE_OF_DEATH_CODE) as code
FROM RAW.dbo.ONS_Death_DOD201516FYQ1 AS a
LEFT OUTER JOIN REFERENCE.dbo.[ONS-PCMD_AGE] AS b
ON a.CALCULATED_AGE_UNIT = b.[Age Unit]
LEFT OUTER JOIN REFERENCE.dbo.[OTH_ICD-10] AS c
ON
CASE
WHEN LEN(
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END) = 3
THEN CONCAT
(
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END, 'X')
ELSE (
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END)
END = c.[4 CharacterDiagnosis Code]
LEFT OUTER JOIN REFERENCE.dbo.GEOG_LANames_1991 AS d
ON a.[ULA_OF_RESIDENCE_CODE] = d.[ONS_District_Code]
LEFT OUTER JOIN REFERENCE.dbo.GEOG_WardNames_2014 AS e
ON CONCAT(a.[ULA_OF_RESIDENCE_CODE],a.[WARD_OF_RESIDENCE_CODE]) =e.WD14CDO
推荐答案
所连接的字段中可能存在空值。我已将ISNULL函数应用于外部联接表中的列。
只需尝试以下格式的代码即可。
There is a chance of null value in the concatenated fields. I have applied ISNULL function to the columns from the outer joined table. Just try with the below formatted code.
SELECT CASE
WHEN a.Sex = 1
THEN 'M'
WHEN a.Sex = 2
THEN 'F'
END AS Derived_Sex,
CASE
WHEN LTRIM(RTRIM(CONCAT(ISNULL(b.Complete,''), a.[CALCULATED_AGE]))) LIKE 'A%'
THEN 'Less than 1'
WHEN LTRIM(RTRIM(concat(ISNULL(b.Complete,''), a.[CALCULATED_AGE]))) LIKE 'B%'
THEN 'Less than 1'
WHEN LTRIM(RTRIM(concat(ISNULL(b.Complete,''), a.[CALCULATED_AGE]))) LIKE 'C%'
THEN 'Less than 1'
ELSE '1 year and above'
END AS Derived_InfantCat,
YEAR(a.DATE_OF_DEATH) AS [Derived_Year of Death],
FORMAT(a.DATE_OF_DEATH, 'MM') AS [Derived_Month of Death],
YEAR(a.DATE_OF_REGISTRATION) AS [Derived_Year of Registration],
FORMAT(a.DATE_OF_REGISTRATION, 'MM') AS [Derived_Month of Registration],
CONCAT(LEFT(CONVERT(nvarchar, a.DATE_OF_DEATH, 21), 4),
SUBSTRING(CONVERT(nvarchar, a.DATE_OF_DEATH, 21), 6, 2)) AS [Derived_YearMonth of Death],
CONCAT(LEFT(CONVERT(nvarchar, a.DATE_OF_REGISTRATION, 21), 4),
SUBSTRING(CONVERT(nvarchar, a.DATE_OF_REGISTRATION, 21), 6, 2)) AS [Derived_YearMonth of Registration],
CASE
WHEN LEN(
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END) = 3
THEN CONCAT(
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END, 'X')
ELSE
(CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END)
END AS [Derived_Cause of Death Code],
c.[4 character DESCRIPTION],
c.[3 character Sub-Chapter Description],
d.[District_Name] AS [LA NAME],
e.[WD14NM] AS [WARD NAME],
(
CASE
WHEN a.[NHS_ESTABLISHMENT_IND] = '1'
THEN 'NHS'
WHEN a.[NHS_ESTABLISHMENT_IND] = '2'
THEN 'NON-NHS'
ELSE 'OTHER'
END) AS [NHS ESTABLISHMENT TYPE DESCRIPTION],
(
CASE
WHEN a.[NHS_ESTABLISHMENT_IND] = '1'
THEN 'NHS'
WHEN a.[NHS_ESTABLISHMENT_IND] = '2'
THEN 'NON-NHS'
ELSE 'OTHER'
END) AS [NHS ESTABLISHMENT TYPE DESCRIPTION],
(
CASE
WHEN a.[NHS_ESTABLISHMENT_IND] = '1'
THEN 'NHS'
WHEN a.[NHS_ESTABLISHMENT_IND] = '2'
THEN 'NON-NHS'
ELSE 'OTHER'
END) AS [NHS ESTABLISHMENT TYPE DESCRIPTION],
(
CASE
WHEN a.PLACE_OF_DEATH_CODE = 'H'
THEN 'HOME'
WHEN a.PLACE_OF_DEATH_CODE = 'E'
THEN 'ELSEWHERE'
ELSE 'OTHER'
END
) AS [PLACE OF DEATH CODE],
concat(a.[NHS_ESTABLISHMENT_IND],'-',a.PLACE_OF_DEATH_CODE) as code
FROM RAW.dbo.ONS_Death_DOD201516FYQ1 AS a
LEFT OUTER JOIN REFERENCE.dbo.[ONS-PCMD_AGE] AS b
ON a.CALCULATED_AGE_UNIT = b.[Age Unit]
LEFT OUTER JOIN REFERENCE.dbo.[OTH_ICD-10] AS c
ON
CASE
WHEN LEN(
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END) = 3
THEN CONCAT
(
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END, 'X')
ELSE (
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END)
END = c.[4 CharacterDiagnosis Code]
LEFT OUTER JOIN REFERENCE.dbo.GEOG_LANames_1991 AS d
ON a.[ULA_OF_RESIDENCE_CODE] = d.[ONS_District_Code]
LEFT OUTER JOIN REFERENCE.dbo.GEOG_WardNames_2014 AS e
ON CONCAT(ISNULL(a.[ULA_OF_RESIDENCE_CODE],''),ISNULL(a.[WARD_OF_RESIDENCE_CODE],'')) =e.WD14CDO
这篇关于错误消息：Concat函数需要2个参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
