CREATE VIEW SD_HOSPITAL.V_OPERATION_SCHEDULE
 (
 PATIENT_ID, VISIT_ID, SCHEDULE_ID, DEPT_STAYED, BED_NO, SCHEDULED_DATE_TIME, 
OPERATING_ROOM, OPERATING_ROOM_NO, SEQUENCE, DIAG_BEFORE_OPERATION, OPERATION_SCALE, 
EMERGENCY_INDICATOR, ISOLATION_INDICATOR, OPERATING_DEPT, SURGEON, FIRST_ASSISTANT, 
SECOND_ASSISTANT, THIRD_ASSISTANT, FOURTH_ASSISTANT, ANESTHESIA_METHOD, 
ANESTHESIA_DOCTOR, ANESTHESIA_ASSISTANT, SECOND_ANESTHESIA_DOCTOR, BLOOD_TRAN_DOCTOR, 
FIRST_OPERATION_NURSE, SECOND_OPERATION_NURSE, FIRST_SUPPLY_NURSE, SECOND_SUPPLY_NURSE, 
THIRD_SUPPLY_NURSE, REQ_DATE_TIME, PATIENT_CONDITION, NOTES_ON_OPERATION, 
OPER_STATUS, SSZB00, GRQK00, BWMC00, BWMC01, YYSSRQ, SSDH00

 )
 AS 
select
  to_char(a.BRID00),
  b.BRRYCS,
  a.SSXH00,
  to_char( nvl(b.dqks00,a.sqks00)),
  b.RYCWH0,

  to_date(a.ssqsrq||a.ssqssj,'YYYYMMDDHH24:MI:SS'),
  nvl(to_char(a.ssks00),'257'),
  Trim(a.ssjh00),
  sstc00,
  (select jbmc00 from xt_icd900 where trim(icd900(+))=trim(a.sqzd00)),

  a.ssdjmc,
  decode(a.jzbz00,'Y',1,0),
  decode(a.GLDJMC,'Õý³£',1,'¸ôÀë',2,'·ÅÉä',3),
  to_char(a.sqks00),
  a.ssysxm,

  SF_ZY_FHCF00(a.sszsxm,';',1),
  SF_ZY_FHCF00(a.sszsxm,';',2),
  SF_ZY_FHCF00(a.sszsxm,';',3),
  SF_ZY_FHCF00(a.sszsxm,';',4),
  SF_ZY_FHCF00(a.mzmc00,';',1),

  SF_ZY_FHCF00(a.mzysxm,';',1),
  SF_ZY_FHCF00(a.mzysxm,';',2),
  SF_ZY_FHCF00(a.mzysxm,';',3),
  SF_ZY_FHCF00(a.sxysxm,';',1),
  SF_ZY_FHCF00(a.tshsxm,';',1),

  SF_ZY_FHCF00(a.tshsxm,';',2),
  SF_ZY_FHCF00(a.gyhsxm,';',1),
  SF_ZY_FHCF00(a.gyhsxm,';',2),
  SF_ZY_FHCF00(a.gyhsxm,';',3),
  to_date(a.sqrq00||a.sqsj00,'YYYYMMDDHH24:MI:SS') ,

  a.sqbqsm,
  a.bz0000,
  a.sszt00,a.sszb00,a.grqk00,a.bwmc00,c.bwmc00,TO_DATE(a.YYSSRQ,'YYYYMMDDHH24:MI:SS'),a.ssdh00

from ss_yw0000 a,zy_brxxb0 b,ss_ywmx00 c
where a.zyghid=b.zyid00(+) and a.ssdh00=c.ssdh00 and
      a.ssqsrq > '20100101' and
      b.BRRYCS is not null
/