SELECT
'PCUCODE','CID','PID','DATEDX','CHRONIC','DATEDIS','TYPEDIS','D_UPDATE'
UNION
SELECT DISTINCT
a.pcucode,a.cid,a.pid,a.datedx,a.chronic,a.datedis,a.typedis,a.d_update
FROM
(
SELECT
h.hospitalcode as pcucode,
p.cid,
p.person_id as pid,
DATE_FORMAT(c.regdate,'%Y%m%d') as datedx,
REPLACE(cc.icd10,'.','') as chronic,
'' as datedis ,
'03' as typedis,
DATE_FORMAT(c.lastupdate,'%Y%m%d%H%i%s') as d_update
FROM
clinicmember c,clinic cc,patient pt,person p,opdconfig h
WHERE c.clinic=cc.clinic AND pt.hn=c.hn AND pt.cid =p.cid
AND cc.chronic='Y' AND c.discharge='N' AND p.house_regist_type_id in(1,3)
AND c.regdate is not null
UNION
SELECT
h.hospitalcode as pcucode,
p.cid,
p.person_id as pid,
DATE_FORMAT(c.regdate,'%Y%m%d') as datedx,
REPLACE(c.icd10,'.','') as chronic,
'' as datedis ,
'03' as typedis,
DATE_FORMAT(c.last_update,'%Y%m%d%H%i%s') as d_update
FROM
person_chronic c,person p,opdconfig h
WHERE
p.house_regist_type_id in(1,3)
AND c.person_id=p.person_id
AND (c.discharge ='N' or c.discharge is NULL)
AND (c.chronic_discharge_status_id is null or c.chronic_discharge_status_id=3)
AND c.regdate is not null
) as a
INTO OUTFILE 'c:/chronic.txt' FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n'