CASE statement in monster query

Question

I am using SQL Server 2012.

I am using the following query to create a custom table used to import into another program. This query is working fine but I need to use a case statement to complete it.

Purpose is the problematic column

view

The current, working query.

WITH Data AS
(   SELECT b.ReportHeading1, 
            p.DisplayOrder, 
            p.MemberCode,
            m.PortfolioGroupCode as DistGroup,
            m.MemberCode as Packgroup,
            g.purpose,
            gg.Purpose as purpose2,
            p.PortfolioGroupID as pid,
            m.PortfolioGroupID as mid,
            convert(varchar(max),lb.value) as repset,
            convert(varchar(max),lb2.value) as periods,
            RowNumber = ROW_NUMBER() OVER(PARTITION BY p.portfoliogroupid ORDER BY p.DisplayOrder)
    FROM    [APXFirm].[AdvApp].[vPortfolioGroupMemberFlattened] p
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioBase] b
                ON b.PortfolioBaseID = p.PortfolioGroupID
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioGroupMember] m
                 on m.MemberID = p.PortfolioGroupID
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioGroup] g
                ON g.PortfolioGroupID = m.PortfolioGroupID
                                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioGroup] gg
                ON gg.PortfolioGroupID = m.MemberCode
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioBaseLabels] lb
                on p.MemberID = lb.PortfolioBaseID
                                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioBaseLabels] lb2
                on p.MemberID = lb2.PortfolioBaseID
                WHERE     m.PortfolioGroupCode like '%_Dist%'
                and (g.Purpose like '%_ind' or g.Purpose like '%group')
                and lb.Label = '$repset'
                and lb2.Label = '$periods'
       )
SELECT
t.ContentSetName,
d.periods as 'DistributionDesc',
    CONCAT(d.pid,'_',d.DisplayOrder,'_',t.ContentSetName,'_',d.ReportHeading1) as DistributionName,
            'False' as IsForFunctionalGroup,
    'True' as IsLandscapePageNum,
    1 as NumOfCopies,
    d.purpose as RecipientCode,
    d.ReportHeading1 as RecipientFullName,
    d.MemberCode as ReportingEntityCode,
    'Daily' as RunEvent
    FROM    Data d
        CROSS APPLY 
        (   VALUES
                ('Cover_SSRS'),
                ('Separator_docx'),
                (d.repset)
                        ) t (ContentSetName)
WHERE   d.RowNumber = 1
OR      t.ContentSetNAme != 'Cover_SSRS'

This produces a result set like this:

ContentSetName  DistributionDesc    DistributionName    IsForFunctionalGroup    IsLandscapePageNum  NumOfCopies RecipientCode   RecipientFullName   ReportingEntityCode RunEvent
Cover_SSRS  135 11221_5178_Cover_SSRS_John Smith Trusts FALSE   TRUE    1   SmithJohn_ind   John Smith Trusts   fial3gen    Daily
Separator_docx  135 11221_5178_Separator_docx_John Smith Trusts FALSE   TRUE    1   SmithJohn_ind   John Smith Trusts   fial3gen    Daily
Report_DVAStandardardAnnSSRS    135 11221_5178_Report_DVAStandardardAnnSSRS_John Smith Trusts   FALSE   TRUE    1   SmithJohn_ind   John Smith Trusts   fial3gen    Daily

I need to add one more column that uses the following CASE statement

CASE WHEN d.purpose = d.purpose2 then d.purpose ELSE  '' END as DistributionCustom

I can get this case statement to work in a smaller query, but in this large query when placing the CASE Statement after this line

d.periods as 'DistributionDesc',

I get this error.

Conversion failed when converting the nvarchar value 'JohnSmith_purpose' to data type int.

I tried this CASE Statement and got the same error result

CASE WHEN convert(nvarchar(max),d.purpose) = convert(nvarchar(max),d.purpose2) then convert(nvarchar(max),d.purpose2) ELSE convert(nvarchar(max),'') END as DistributionCustom

Thank you in advance

Seems like a data type mismatch while comparing d.purpose and d.purpose2. Could you share your table definition? — Suyash Khandwe, Jan 12 '14 at 00:45

Suyash Khandwe · Accepted Answer · 2014-01-12T01:29:46.793

1

Since you have LEFT OUTER JOINS, there would definitely be NULLS. In SQL Server a temp column is given INT datatype if it has NULL before any alpha-numeric values -

You can try changing your Data fields like this -

SELECT b.ReportHeading1, 
            p.DisplayOrder, 
            p.MemberCode,
            m.PortfolioGroupCode as DistGroup,
            m.MemberCode as Packgroup,
            ISNULL(g.purpose,'') AS purpose,
            ISNULL(gg.Purpose,'') as purpose2,
...

Update 1 - Try changing your case statement to this -

CASE    WHEN ISNULL(d.purpose,'.') = ISNULL(d.purpose2,'.')
    THEN CAST(d.purpose2 AS NVARCHAR(MAX))
    ELSE '' END AS DistributionCustom

edited Jan 12 '14 at 01:29

answered Jan 12 '14 at 00:51

Suyash Khandwe

386
3
11

I tried your suggestion. Unfortunately still getting the error. I added an image of the table details too. – Jay C Jan 12 '14 at 01:17
1

Could you try the updated comment in the answer above ? – Suyash Khandwe Jan 12 '14 at 01:28

CASE statement in monster query

1 Answers1