sql - Using a Case Statement in a Function -

i having issues piece of query writing- i'm creating indicator field want show '1' if couple of cases satisfied. field built using case statement inside max() function- need function because portion part of cte joined cte turning rows columns , used functions lateralize.

 max(case      when pmod1_pgm in ('ir') , integer(pmod1_pct) <> 0        1      when pmod2_pgm in ('ir') , integer(pmod2_pct) <> 0        1      when pmod3_pgm in ('ir') , integer(pmod3_pct) <> 0        1      when pmod4_pgm in ('ir') , integer(pmod4_pct) <> 0        1      when pmod5_pgm in ('ir') , integer(pmod5_pct) <> 0        1      when pmod6_pgm in ('ir') , integer(pmod6_pct) <> 0        1      else 0 end) @irpm_ind,       /*@irpm_pct*/  max(case      when pmod1_pgm in ('ir') , pmod1_cd in ('c')        pmod1_pct * - 1      when pmod1_pgm in ('ir') , pmod1_cd in ('d')        pmod1_pct      when pmod2_pgm in ('ir') , pmod1_cd in ('c')        pmod2_pct * - 1      when pmod2_pgm in ('ir') , pmod1_cd in ('d')        pmod2_pct      when pmod3_pgm in ('ir') , pmod1_cd in ('c')        pmod3_pct * - 1      when pmod3_pgm in ('ir') , pmod1_cd in ('d')        pmod3_pct      when pmod4_pgm in ('ir') , pmod1_cd in ('c')        pmod4_pct * - 1      when pmod4_pgm in ('ir') , pmod1_cd in ('d')        pmod4_pct      when pmod5_pgm in ('ir') , pmod1_cd in ('c')        pmod5_pct * - 1      when pmod5_pgm in ('ir') , pmod1_cd in ('d')        pmod5_pct      when pmod6_pgm in ('ir') , pmod1_cd in ('c')        pmod6_pct * - 1      when pmod6_pgm in ('ir') , pmod1_cd in ('c')        pmod6_pct      else 0 end) @irpm_pct 

basically check 1 field see if 'ir' present , percent not equal 0- if want indicator of 1. when run this, there still entries coming through indicator of 1 , @irpm_pct of 0. advice? have case statements being in max() function? when check data comes through, seems "work" eliminate these indicator of 1, percent of 0 cases , thought way built indicator case statement deal this.

in irpm_pct case statement, check pmod6_pgm 'c' twice (instead of 'c' , 'd').

so if pmod6_pgm = 'ir', pmod6_pct non-zero, , pmod1_cd = 'd', you'll irpm_ind = 1 , irpm_pct = 0 (because pmod1_cd in ('d') not in irpm_pct case statement).

so change last pmod1_cd in ('c') pmod1_cd in ('d').