SELECT  a.patient_id
      , a.HOSPITAL_PATIENT_ID
      , a.PATIENT_NATIVENAME
      , a.GENDER
      , a.DATE_DIAGNOSIS
      , MAX( b.response)
      , COUNT(c.date_start)
      , MAX(TO_CHAR(c.date_start,'YYYY-MM-DD')||RPAD(c.name,10, ' ')||RPAD(b.dose||' '||b.TIMES_DAY,15,' ')||c.date_stop)
   FROM acr_patient a, acr_response b, ACR_TXDRUG c, acr_code d
  WHERE a.patient_id = b.patient_id(+)
    AND b.type(+) = 'hr'
    AND a.DATE_DIAGNOSIS = b.date_response(+)
    AND a.patient_id = c.patient_id
    AND d.code ='drug'
    AND c.drug_code = d.code
   GROUP BY a.patient_id
      , a.HOSPITAL_PATIENT_ID
      , a.PATIENT_NATIVENAME
      , a.GENDER
      , a.DATE_DIAGNOSIS;
	  
	  
query1 += "NVL(MAX(tki.renice_yn),'Non-clinical trial') clinical, p.hospital_patient_id, to_char(p.date_diagnosis,'DD-MON-YYYY') dx_date ";
  query1 += " FROM  (  SELECT  p.patient_id, p.lastname||' '||p.firstname||' '||p.middlename fullname, age, gender, date_diagnosis,hospital_patient_id, ";
  query1 += "                  MAX(DECODE (drg.drug_code, 'd002', 'Yes', 'No')) drug_code , MIN(drg.date_start) date_start";
  query1 += "            FROM acr_patient p, acr_txdrug drg ";
  query1 += "            WHERE center_id = '"+centerCode+"' ";
   /*
      query1 += "AND EXISTS ( SELECT 1 FROM ACR_TXDRUG xx WHERE xx.patient_id = p.patient_id AND  xx.drug_code = 'Ponatinib')  
 */
  query1 += "             AND del IS NULL  AND ";



select x.patient_id
	     , x.date_visit
	     , ROW_NUMBER() OVER(PARTITION BY x.patient_id ORDER BY x.patient_id, x.date_visit) seq
	     , MAX(x.drug_code)
	     , DECODE(MAX(x.dose), 0, '', MAX(x.dose))
	     , MAX(x.dose_unit)
	     , MAX(x.TIMES_DAY)
	     , MAX(x.wbc)
	     , MAX(x.ANC)
	     , MAX(x.HEMOGLOBIN)
	     , MAX(x.PLATELET)
	     , MAX(x.BASOPHILS) 
	     , MAX(x.PROMYELOCYTE) 
	     , MAX(x.BLASTS) 
	     , MAX(x.LYMPHOCYTES)
	     , MAX(x.EOSINOPHILS)
	     , MAX(x.METAMYELOCYTE)
	     , MAX(x.MONOCYTES)
	     , MAX(x.PH_CHROMOSOME)
	     , DECODE(MAX(x.NON_CLASSICAL_PH), ' ', '0', MAX(x.NON_CLASSICAL_PH) ) as NON_CLASSICAL_PH
	     , MAX(x.BM_BLASTS)
	     , DECODE(MAX(x.BM_BASOPHILS), ' ', '0', MAX(x.BM_BASOPHILS) ) as BM_BASOPHILS
	     , MAX(x.IS_ABL)
	     , DECODE(MAX(x.MUTANT), ' ', '0', MAX(x.MUTANT)) as mutant
	     , replace(SUBSTR(XMLAGG(XMLELEMENT(K,',' || x.grade) ORDER BY x.patient_id,x.date_visit, x.grade desc ).EXTRACT('//text()'), 2), ',,', '') as grade
	     , replace(SUBSTR(XMLAGG(XMLELEMENT(K,',' || x.AE_CODE) ORDER BY x.patient_id,x.date_visit, x.grade desc ).EXTRACT('//text()'), 2),',,', '') as ae_code
	     , replace(SUBSTR(XMLAGG(XMLELEMENT(K,',' || x.ae_category) ORDER BY x.patient_id,x.date_visit, x.grade desc ).EXTRACT('//text()'), 2),',,', '') as ae_category
	     , replace(SUBSTR(XMLAGG(XMLELEMENT(K,',' || x.ae_OTHERS) ORDER BY x.patient_id,x.date_visit, x.grade desc ).EXTRACT('//text()'), 2),',,', '') as ae_others 
--		 , WM_CONCAT(x.grade) OVER (ORDER BY x.grade) as grade2
	--	 , WM_CONCAT(x.AE_CODE)  as ae_code2
		-- , WM_CONCAT(x.ae_category) as ae_category2
	  from (	
				select 0 as rnum, a.patient_id, a.drug_code, a.date_start as date_visit, a.dose, a.dose_unit, TIMES_DAY,  '' wbc, '' ANC, '' HEMOGLOBIN, '' PLATELET, '' BASOPHILS, '' PROMYELOCYTE, '' BLASTS, '' LYMPHOCYTES, '' EOSINOPHILS, '' METAMYELOCYTE, '' MONOCYTES, '' as PH_CHROMOSOME, '' NON_CLASSICAL_PH, '' BM_BLASTS, '' BM_BASOPHILS, '' IS_ABL, '' MUTANT, '' GRADE, ''AE_CODE, '' AE_CATEGORY, '' AE_OTHERS 
				from acr_txdrug a
				where a.patient_id >= 'KRCMC01000'
				union all
				select 0 as rnum, a.patient_id, a.drug_code, a.date_stop as date_visit, a.dose, a.dose_unit, TIMES_DAY, '' wbc, '' ANC, '' HEMOGLOBIN, '' PLATELET, '' BASOPHILS, '' PROMYELOCYTE, '' BLASTS,'' LYMPHOCYTES, '' EOSINOPHILS, '' METAMYELOCYTE, '' MONOCYTES,'' as PH_CHROMOSOME, '' NON_CLASSICAL_PH, '' BM_BLASTS, '' BM_BASOPHILS, '' IS_ABL, '' MUTANT, '' GRADE, ''AE_CODE, '' AE_CATEGORY, '' AE_OTHERS 
				from acr_txdrug a
				where a.patient_id >= 'KRCMC01000'
				union all
				select 0 as rnum, patient_id, '', date_test as date_visit, 0 , '', '', wbc, ANC, HEMOGLOBIN, TO_CHAR(PLATELET), TO_CHAR(BASOPHILS), TO_CHAR(PROMYELOCYTE), TO_CHAR(BLASTS), TO_CHAR(LYMPHOCYTES), TO_CHAR(EOSINOPHILS), TO_CHAR(METAMYELOCYTE), TO_CHAR(MONOCYTES), '' as PH_CHROMOSOME,  '' NON_CLASSICAL_PH, '' BM_BLASTS, '' BM_BASOPHILS, '' IS_ABL, '' MUTANT, '' GRADE, ''AE_CODE, '' AE_CATEGORY, '' AE_OTHERS 
				from ACR_PERIPHERAL_BLOOD
				where lab IN ('C','c') and  patient_id >= 'KRCMC01000'
				union all
				select 0 as rnum,  patient_id, '', date_test as date_visit, 0 , '', '', '' wbc, '' ANC, '' HEMOGLOBIN, '' PLATELET, '' BASOPHILS, '' PROMYELOCYTE, '' BLASTS, '' LYMPHOCYTES, '' EOSINOPHILS, '' METAMYELOCYTE, '' MONOCYTES, PH_CHROMOSOME, NVL(TO_CHAR(NON_CLASSICAL_PH), ' '), '' BM_BLASTS, '' BM_BASOPHILS, '' IS_ABL, '' MUTANT, '' GRADE, ''AE_CODE, '' AE_CATEGORY, '' AE_OTHERS 
				from ACR_CYTOGENETIC
				where lab IN ('C','c') and  patient_id >= 'KRCMC01000' 
				union all
				select 0 as rnum, patient_id, '', date_test as date_visit, 0 , '', '', '' wbc, '' ANC, '' HEMOGLOBIN, '' PLATELET, '' BASOPHILS, '' PROMYELOCYTE, '' BLASTS, ''LYMPHOCYTES, '' EOSINOPHILS, '' METAMYELOCYTE, '' MONOCYTES, '' PH_CHROMOSOME, '' NON_CLASSICAL_PH, '' BM_BLASTS, '' BM_BASOPHILS, IS_ABL, '' MUTANT, '' GRADE, ''AE_CODE, '' AE_CATEGORY, '' AE_OTHERS 
				from ACR_RQ_PCR
				where lab IN ('C','c', 'O') and  patient_id >= 'KRCMC01000'
				union all
				select 0 as rnum, patient_id, '', date_test as date_visit, 0 , '', '', '' wbc, '' ANC, '' HEMOGLOBIN, '' PLATELET, '' BASOPHILS, '' PROMYELOCYTE, '' BLASTS, ''LYMPHOCYTES, '' EOSINOPHILS, '' METAMYELOCYTE, '' MONOCYTES, '' PH_CHROMOSOME,  '' NON_CLASSICAL_PH,  blasts as BM_BLASTS,  NVL(BASOPHILS, ' ') as BM_BASOPHILS, '' IS_ABL, '' MUTANT, '' GRADE, ''AE_CODE, '' AE_CATEGORY, '' AE_OTHERS 
				from ACR_BM_PATHOLOGY
				where lab IN ('C','c') and  patient_id >= 'KRCMC01000'
				union all
				select  0 as rnum, patient_id, '', date_test as date_visit, 0 , '', '', '' wbc, '' ANC, '' HEMOGLOBIN, '' PLATELET, '' BASOPHILS, '' PROMYELOCYTE, '' BLASTS, ''LYMPHOCYTES, '' EOSINOPHILS, '' METAMYELOCYTE, '' MONOCYTES, '' PH_CHROMOSOME,  '' NON_CLASSICAL_PH, '' BM_BLASTS, '' BM_BASOPHILS, '' IS_ABL, NVL(MUTANT,' '), '' GRADE, ''AE_CODE, '' AE_CATEGORY, '' AE_OTHERS
				from ACR_MUTANT 
				where lab IN ('C','c') and  patient_id >= 'KRCMC01000'
				union all 
				select row_number() over( partition by y.patient_id , date_visit order by NVL(grade,0) desc)  as rnum, y.patient_id, y.drug_code, y.date_visit, y.dose , y.dose_unit , y.TIMES_DAY, y.wbc, y.ANC, y.HEMOGLOBIN, y.PLATELET, y. BASOPHILS, y.PROMYELOCYTE, y. BLASTS, y.LYMPHOCYTES, y.EOSINOPHILS, y.METAMYELOCYTE, y.MONOCYTES, y.PH_CHROMOSOME,  y.NON_CLASSICAL_PH, y.BM_BLASTS, y.BM_BASOPHILS, y.IS_ABL, y. MUTANT, y.GRADE, y.AE_CODE, y.AE_CATEGORY, y.AE_OTHERS
				  from (select  a.patient_id, '' as drug_code, a.date_stop as date_visit, 0 as dose , '' as dose_unit , '' as TIMES_DAY, '' wbc, '' ANC, '' HEMOGLOBIN, '' PLATELET, '' BASOPHILS, '' PROMYELOCYTE, '' BLASTS, '' LYMPHOCYTES, '' EOSINOPHILS, '' METAMYELOCYTE, '' MONOCYTES, '' PH_CHROMOSOME,  '' NON_CLASSICAL_PH, '' BM_BLASTS, '' BM_BASOPHILS, '' IS_ABL, '' MUTANT, f.grade as GRADE, f.ae_code as AE_CODE, f.category as AE_CATEGORY, f.SPECIFY as AE_OTHERS 
						 from acr_txdrug a, acr_ae f
						WHERE a.patient_id >= 'KRCMC01000' and a.patient_id = f.patient_id(+) and a.txdrug_id = f.txdrug_id(+) and f.drug_gubun(+) = '9'
						ORDER BY grade desc
					   ) y
		  ) x
   where x.patient_id like 'KRCMC%'
 --    and x.patient_id = 'KRCMC02349'
     and  x.rnum < 4
 group by  x.patient_id
	     , x.date_visit
 order by 1, 2