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 )