Skip to content
Advertisement

Case Statement within in Statement in MS SQL

I want to compare two different tables in IN statement based on condition.

SELECT DISTINCT
        cts.ContactType
        ,ISNULL(ctl.[Description], CONCAT('Unknown Contact Type: ',cts.ContactType)) AS [Description]
FROM
                (       SELECT Null AS [ContactType]
                        WHERE ISNULL(Null, '') <> ''
                UNION ALL
                        SELECT DISTINCT cta.ContactType
                        FROM
                                        (SELECT u.AccessUserID
                                                FROM v2010_vUsers u
                                                WHERE u.UserID = 'harry_1@cared_t'
                                        UNION ALL
                                                SELECT ur.AccessUserID
                                                FROM v2010_UserRoles ur
                                                WHERE ur.UserID = 'harry_1@cared_t') r --Roles user has
                                INNER JOIN v2010_UDV_ContactType_AccessMatrix_Full cta --ContactTypes Accessible
                                ON cta.AccessUserID = r.AccessUserID) cts --ContactTypes Selectable
        LEFT JOIN v2010_ListEntries ctl --ContactType List
                ON ctl.ListId='CONTACTTYPE' and ctl.Code = cts.ContactType 
                WHERE cts.ContactType In 
                (
                    CASE WHEN (SELECT dbo.f2010_IsFeatureAllowedInTenancy('ContactTypeSC', dbo.f2010_PartnerDivisionID('06b30841-2496-48ef-b91a-21f06a9299f2'))) = '1'
                        THEN (SELECT Code From v2010_ListEntries WHERE ListId='CONTACTTYPESC')
                        ELSE (SELECT Code From v2010_ListEntries WHERE ListId='CONTACTTYPE')
                    END   
                )
            

ERROR : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Error is in following lines:

WHERE cts.ContactType In 
                (
                    CASE WHEN (SELECT dbo.f2010_IsFeatureAllowedInTenancy('ContactTypeSC', dbo.f2010_PartnerDivisionID('06b30841-2496-48ef-b91a-21f06a9299f2'))) = '1'
                        THEN (SELECT Code From v2010_ListEntries WHERE ListId='CONTACTTYPESC')
                        ELSE (SELECT Code From v2010_ListEntries WHERE ListId='CONTACTTYPE')
                    END   
                )

I am not able to use case when in IN statement. if I write WHERE cts.ContactType In (SELECT Code From v2010_ListEntries WHERE ListId=’CONTACTTYPESC’) it is working but I want to compare condition and based on condition compare table.

Advertisement

Answer

It is a bit hard to tell where the multiple rows problem is coming from, but I am thinking:

WHERE (dbo.f2010_IsFeatureAllowedInTenancy('ContactTypeSC', dbo.f2010_PartnerDivisionID('06b30841-2496-48ef-b91a-21f06a9299f2')) = '1' and
       cts.ContactType In (SELECT Code From v2010_ListEntries WHERE ListId = 'CONTACTTYPESC')
      ) OR
      (dbo.f2010_IsFeatureAllowedInTenancy('ContactTypeSC', dbo.f2010_PartnerDivisionID('06b30841-2496-48ef-b91a-21f06a9299f2')) <> '1' and
       cts.ContactType In (SELECT Code From v2010_ListEntries WHERE ListId = 'CONTACTTYPE'')
      )

Or, more simply as:

cts.ContactType In (SELECT le.Code
                    FROM v2010_ListEntries le CROSS JOIN
                         (VALUES (CASE WHEN dbo.f2010_IsFeatureAllowedInTenancy('ContactTypeSC', dbo.f2010_PartnerDivisionID('06b30841-2496-48ef-b91a-21f06a9299f2')) THEN 'CONTACTTYPESC' ELSE 'CONTACTTYPESC' END)) v(listId)
                    WHERE le.ListId = v.ListId
                   )


  
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement