|
SELECT a.emplid , a.address1 , CASE WHEN a.address2 <>' ' THEN a.address1 %Concat ', ' %Concat a.address2 ELSE a.address1 END , CASE WHEN a.address3 <>' ' AND a.address4<>' ' THEN a.address3 %Concat ', ' %Concat a.address4 WHEN a.address3 <>' ' AND a.address4=' ' THEN a.address3 WHEN a.address3=' ' AND a.address4<>' ' THEN a.address4 ELSE ' ' END , CASE WHEN a.county <>' ' AND a.city<> ' ' THEN a.city %Concat ', ' %Concat ( SELECT descr FROM %Table(state_tbl) b WHERE b.state=a.county AND b.country=a.country ) WHEN a.county <>' ' AND a.city= ' ' THEN ( SELECT descr FROM %Table(state_tbl) b WHERE b.state=a.county AND b.country=a.country ) ELSE a.city END , CASE WHEN a.postal<>' ' THEN ( SELECT descr FROM %Table(state_tbl) b WHERE b.state=a.state AND b.country=a.country ) %Concat ', ' %Concat a.postal ELSE ( SELECT descr FROM %Table(state_tbl) b WHERE b.state=a.state AND b.country=a.country ) END , ( SELECT descr FROM %Table(country_tbl) WHERE country=a.country ) FROM %Table(hcr_per_addr_i) a WHERE a.address_type = 'HOME' AND a.effdt = ( SELECT MAX(effdt) FROM %Table(hcr_per_addr_i) a1 WHERE a.emplid = a1.emplid AND a1.address_type = 'HOME')
|
EMPLID