mondaymorning mondaymorning - 3 months ago 24
SQL Question

Error Message: Concat function requires 2 arguments

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.

Error Message

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

Answer

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
Comments