SELECT
 CASE 
  WHEN s.agntnum IS NOT NULL AND  'GAO' IN ('AD') THEN 'GAO' 
  ELSE a.agtype
 END agentType,   
 a.agntnum agentNumber, c.surname, z.rinternet emailAddress, z.mblphone01 mobilePhone, ag.tsalesunt officeCode
FROM stg.agntpf AS a 
 INNER JOIN stg.clntpf AS c ON a.clntnum = c.clntnum
 INNER JOIN stg.Zagnpf AS z ON z.agntnum = a.agntnum AND z.clntnum = a.clntnum
 INNER JOIN stg.Aglfpf AS ag ON a.agntnum = ag.agntnum
 LEFT OUTER JOIN stg.Sagof s ON s.agntnum = a.agntnum AND 'GAO' IN ('AD')
WHERE z.validflag = '1' 
 AND (('AD') IS NULL OR
   CASE 
    WHEN s.agntnum IS NOT NULL AND 'GAO' IN ('AD') THEN 'GAO'
    ELSE a.agtype
   END IN ('AD'))
 AND (('AT1') IS NULL OR ag.tsalesunt IN ('AT1'))
 AND ('00001273' IS NULL OR a.agntnum LIKE '001273%' OR LOWER(c.surname) LIKE LOWER('001273%'))



SELECT DISTINCT ag.tsalesunt officeCode
FROM stg.agntpf a
 INNER JOIN stg.Aglfpf ag ON a.agntnum = ag.agntnum
WHERE a.agentType IN ('AG', 'AA', 'AD', 'UM', 'SU', 'AM', 'MA')
 AND (('AG') IS NULL OR LOWER(ag.officeCode) LIKE 'AG')
ORDER BY ag.officeCode