I am creating a SQL query having WHERE CASE WHEN
statement. I am doing something wrong and getting error.
My SQL statement is like
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) )