CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ADC
ADC

Reputation: 617

SQL Server Query select case when

I have to select different ItemID on different two cases.

In the first case I am selecting the ItemID = @OrgID that is the organisation parent because these doesn't have the ParentOrganisationId (is null) -- then this case work fine.

I have problem in the second case where I am checking if it is a branch then take with the subquery the ParentOrganisationId to bring = ItemID in the first where condition. This second case get null value.

Declare @OrgID int
Set @OrgID = 91702 --- 91702(Branch Organisation), 83279(Parent Organisation)

select ItemID 
FROM Organisations 
where ItemID = case
                  when ParentOrganisationId is null 
                    then @OrgID --case without branches
                  when ParentOrganisationId is not null 
                    then (select ParentOrganisationId 
                          from Organisations 
                          where ItemID = @OrgID) --case with branches
               end

Upvotes: 2

Views: 103

Answers (2)

Arvo
Arvo

Reputation: 10570

Doesn't next query work for you?

select coalesce(ParentOrganisationId, ItemId) as ItemID
from Organisations
where ItemID = @OrdID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

case statement returns a scalar expression, not a list. Remove the case and express the logic using basic boolean operators:

select ItemID 
FROM Organisations
where (ParentOrganisationId is null and ItemID = @OrgID) or
      (ParentOrganisationId is not null and
       ItemID in (select ParentOrganisationId from Organisations where ItemID = @OrgID)
      );

Upvotes: 4

Related Questions