Select case when variable = something then ( with not null)

1

I can't figure this out.
I need to select a value if a contrition = 1 and is null if not.
So

select * from etc etc
where ......

---Here
and    ZZ_vwConsCollectedDTL.CollectedWorkStationCode 
     case
     when (@CollectedWorkStationCode = -1) then not null   
      else  @CollectedWorkStationCode
      end

So what should i do in this case?
Thanks.

so if @CollectedWorkStationCode = -1 then "and" will be and ZZ_vwConsCollectedDTL.CollectedWorkStationCode is not null
else the "and" will be and ZZ_vwConsCollectedDTL.CollectedWorkStationCode = @CollectedWorkStationCode

2

IIUC something like:

where @var = -1 and col is not null or @var <> -1 and col = @var

1 Like
3

Hi.
Thanks.
Can I also bring + the NULL values if the @var = -1 ?

4

I think I got that:
and @CollectedUserCode = -1 and (ZZ_vwConsCollectedDTL.CollectedUserCode is not null or ZZ_vwConsCollectedDTL.CollectedUserCode is null) or @CollectedUserCode <> -1 and ZZ_vwConsCollectedDTL.CollectedUserCode = @CollectedUserCode

5

II think this is better

6

looks good to me. does it work?

7

yep!!