title: Case statement and Joins
body: <p>Looking at DN7 statement. Guess I am missing somethingin the join area. </p>
  
  <p>Need case to display company.name from company table when ca.code = 128 
  or 
  When ca.code is 106 carrier.name is displayed.<br>
  else ph.name</p>
  
  <p>select</p>
  
  <pre><code>--TRANSACATION INFO FOR HT
  '148' as 'DN1', --TRANSACTION SET ID
  isnull(convert(char(2), d9.code), space(2)) as 'D2', --MAINTENANCE TYPE CODE
  isnull(left(convert(char(8), c2.froimaintdate, 112), 8), space(8)) as 'DN3c', ---MAINTENANCE TYPE CODE DATE 
  
  --STATE AGENCY
  left(c1.jurst, 2) as 'DN4',  --Jurisdiction Code
  isnull(convert(char(25), c1.agencynumb), space(25)) as 'DN5', --Claim Number
  
  --INSURER/CARRIER/SELF-INSURER
   case
    when ca.invmsg like 'CARRIER%' then isnull(left(uw.fedid, 9), space(9))
    else left(ph.fedid, 9)
   end 
   as 'DN6', --Carrier/Self-Insurer FEIN
   case
    when ca.invmsg like 'CARRIER%' and ca.code ='0128' then isnull(convert(char(30), (ca.name)), space(30))
    when ca.invmsg like 'CARRIER%' and ca.code ='0106' then isnull(convert(char(30), (uw.name)), space(30))
    else convert(char(30), (ph.name))
   end 
   as 'DN7', --Carrier/Self-Insurer Name**
  
      from claim c
  join claim1 c1 on c.claimno = c1.lnkclaimno
  join claim2 c2 on c.claimno = c2.link2claim
  join claim3 c3 on c.claimno = c3.link3claim
  join person p on c.patient = p.personid
  join employer e on c.empcode = e.code
  join cmpolicy cm on c.claimno = cm.claimno
  join policy po on cm.policyid = po.policyid
  join carrier ca on c.carrier = ca.code
  join masttype m on c.casetype = m.mastid
  left join company uw on po.uwcompany = uw.code
  left join [address] a on c.occurlocat = a.addrid
  left join employer ph on e.polholder = ph.code
  left join mmi1 mm on c.claimno = mm.claimno
  left join dictdata d1 on ph.siccode = d1.datumid        --SIC Code
  left join dictdata d2 on c2.claimncci = d2.datumid      --NCCI Class (Occ) Code
  left join dictdata d3 on c1.injurnatur = d3.datumid     --Nature of Injury
  left join dictdata d4 on c.bodypartid = d4.datumid      --Body Part
  left join dictdata d5 on c1.injurcause = d5.datumid     --Cause of Injury
  left join dictdata d6 on c1.empstatus = d6.datumid      --Employment Status
  left join dictdata d7 on c2.losstype = d7.datumid       --Type Of Loss Code 
  left join dictdata d8 on c2.mcotype = d8.datumid        --Managed Care Organization Code
  left join dictdata d9 on c2.froimaintcode = d9.datumid  --Maintenance Type Code
  left join authent au on c1.adjuster = au.shortname      --Adjuster
  left join usermst u on au.authentid = u.personid        --Adjuster
  left join authent au2 on c.username = au2.shortname     --User (Preparer)
  left join usermst u2 on au2.authentid = u2.personid     --User (Preparer)
  left join provider pv on c3.treatprovider = pv.fedidseq --Initial Treatment Provider
  left join v_provaddr_master v on pv.fedidseq = v.fedidseq   --Initial Treatment Provider Address
  left join cf_data cf1 on c.claimno = cf1.claimno and cf1.cf_id = '00024'    --Notice Date
  
  
  --Date Function
  WHERE froimaintdate = '2016-10-26'
          and c1.jurst = 'TX'
  </code></pre>