kspyy
kspyy

Reputation: 71

Omit records based off of case statement

This is for an address list report I have. Basically Clients and programs. A client can be discharged from one program but still active in another, so I want to omit these records for a certain one program. Right now i'm trying to do something like this in my select:

case prg.Program
when 'AES' then c.clientid not in (2938,30495,4958)
else prg.Program
end as test

Basically I want these records to not show up when the program is AES. But if they have another program I want them to still come up. Is this possible?

Also program and clientid are both declared as variables, don't know if that might help

Upvotes: 0

Views: 2106

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You might want something like this:

(case when prg.InterAct_Program <> 'AES' or c.clientid not in (2938, 30495, 4958)
      then prg.InterAct_Program
 end)

This will return NULL for the value.

You might also want a where clause:

where (prg.InterAct_Program <> 'AES' or c.clientid not in (2938, 30495, 4958))

This will actually filter out the rows.

Upvotes: 2

Related Questions