I am creating a SQL query having WHERE CASE WHEN
statement. I am doing something wrong and getting error.
My SQL statement is like
x
DECLARE @AreaId INT = 2
DECLARE @Areas Table(AreaId int)
INSERT INTO @Areas SELECT AreaId
FROM AreaMaster
WHERE CityZoneId IN (SELECT CityZoneId FROM AreaMaster WHERE AreaId = @AreaID)
SELECT *
FROM dbo.CompanyMaster
WHERE AreaId IN
(CASE WHEN EXISTS (SELECT BusinessId
FROM dbo.AreaSubscription
WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId)
THEN @AreaId
ELSE (SELECT [@Areas].AreaId FROM @Areas)
END)
I am getting error as
Msg 512, Level 16, State 1, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Please help to successfully run query. My logic is to checking for conditional AreaId
in (statement) for each row.
I want to select the row only when
- company has subscription entry into
AreaSubscription
for specific area passed by@AreaId
- table
AreaSubscription
does not have subscription entry then evaluateAreaId
in(SELECT [@Areas].AreaId FROM @Areas)
Advertisement
Answer
This may help you.
SELECT * FROM dbo.CompanyMaster
WHERE AreaId=
(CASE WHEN EXISTS (SELECT BusinessId
FROM dbo.AreaSubscription
WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId)
THEN @AreaId ELSE AreaId END)
AND AreaId IN (SELECT [@Areas].AreaId FROM @Areas)
One more solution is
SELECT * FROM dbo.CompanyMaster A
LEFT JOIN @Areas B ON A.AreaId=B.AreaID
WHERE A.AreaId=
(CASE WHEN EXISTS (SELECT BusinessId
FROM dbo.AreaSubscription
WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId)
THEN @AreaId ELSE B.AreaId END)
)