SELECT 'cid','pname','fname','lname','birth','sex','address','moo','aid','unit','va_r','va_l','va_date','hoscode'
UNION
select * from
(
SELECT p.idcard cid,ctitle.titlename pname,p.fname,p.lname,p.birth,p.sex,house.hno address,village.villno moo,
left(house.villcode,6)aid,'F'unit ,
case WHEN v2.eyerightva = '20/200' THEN '1'   
WHEN v2.eyerightva = '20/100' THEN '2'
WHEN v2.eyerightva = '20/70' THEN '3'
WHEN v2.eyerightva = '20/50' THEN '4'
WHEN v2.eyerightva = '20/40' THEN '5' 
WHEN v2.eyerightva = '20/30' THEN '6'
WHEN v2.eyerightva = 'CF3' THEN '7' 
WHEN v2.eyerightva = 'CF2' THEN '8'
WHEN v2.eyerightva = 'CF1' THEN '9'
WHEN v2.eyerightva = 'HM' 	THEN '10'	
WHEN v2.eyerightva = 'PJ' THEN '11'
WHEN v2.eyerightva = 'PL' THEN '12'
WHEN v2.eyerightva = 'no PL' THEN '13'
WHEN v2.eyerightva = '20/20' THEN '14' ELSE '' END  va_r,
case WHEN v2.eyeleftva = '20/200' THEN '1'   
WHEN v2.eyeleftva = '20/100' THEN '2'
WHEN v2.eyeleftva = '20/70' THEN '3'
WHEN v2.eyeleftva = '20/50' THEN '4'
WHEN v2.eyeleftva = '20/40' THEN '5' 
WHEN v2.eyeleftva = '20/30' THEN '6'
WHEN v2.eyeleftva = 'CF3' THEN '7' 
WHEN v2.eyeleftva = 'CF2' THEN '8'
WHEN v2.eyeleftva = 'CF1' THEN '9'
WHEN v2.eyeleftva = 'HM' 	THEN '10'	
WHEN v2.eyeleftva = 'PJ' THEN '11'
WHEN v2.eyeleftva = 'PL' THEN '12'
WHEN v2.eyeleftva = 'no PL' THEN '13'
WHEN v2.eyeleftva = '20/20' THEN '14' ELSE '' END  va_l,
v.visitdate,v.pcucode
FROM visit v
LEFT JOIN visitcataractofficer v2 on v.pcucode=v2.pcucode AND v.visitno=v2.visitno
INNER JOIN visitdiag vd ON vd.pcucode = v.pcucode AND vd.visitno = v.visitno
INNER JOIN person p ON v.pcucodeperson = p.pcucodeperson AND v.pid = p.pid
left join ctitle on p.prename = ctitle.titlecode
inner join house on p.hcode = house.hcode and p.pcucodeperson = house.pcucode
inner join village on house.villcode = village.villcode and house.pcucode = village.pcucode
where getAgeYearNum(p.birth, '2015-10-01') >= 60 and
#vd.diagcode IN ('H54.5','H54.1','H54.4','H54.0') and 
v.visitdate BETWEEN '2015-10-01' and '2016-09-30'
GROUP BY p.pid
)a
INTO OUTFILE 'C:\\JHCIS_export\\vaid.txt'
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\r\n';
