The create or replace procedure GetEmergencyList_test 
(
BdSrc in date, date - emergency 
EdSrc in date, date - emergency 
Emp_sn in varchar2, - operation person 
Emer_stateV in varchar2, - emergency signs (null returns all) 
Dept_sn in varchar2, - emergency department 
Shift_id int, in - flight (no use) 
Querytype in int:=0 -- query types (0: the nurse station, 1: doctor stand) 
) is 
Bd date; 
Ed the date; 
Bd4Hour date; 
The begin 

Bd4Hour:=sysdate - 4/24; 

If querytype & lt;> 0 then 
Ed:=to_date (TO_CHAR (sysdate, 'YYYY - MM - DD) +' 23:59:59 ', '- DD YYYY - MM HH24: MI: SS'); 
Bd:=sysdate - 4/24; 
end if; 

If querytype=0 then 
Ed:=to_date (TO_CHAR (edSrc, 'YYYY - MM - DD) +' 23:59:59 ', '- DD YYYY - MM HH24: MI: SS'); 
Bd:=to_date (TO_CHAR (bdSrc, 'YYYY - MM - DD) +' 00:00:00 ', '- DD YYYY - MM HH24: MI: SS'); - is set to the nurse station queries all day 
end if; 

Select * from (
Select 
Amy polumbo _id, - the patient id 
A.t imes,, 
 The Times ofA. - "NUMBER", the queue NUMBER 
A.e mer_id - emergency id 
A.o perator_date as emer_date - triage time 
A. d. ept_sn - triage department 
A.e mer_state - emergency sign 
Arjun an_grade - condition classification 
A.o perator, - triage person number 
A.o perator_date - save time 
A.n otice_dept, notify the department 
A.n otice_date - notice 
A. d. octor_sn, - the doctor number 
Dr. Isit_date - time 
A.o bserved_bd - under observation start time 
A.o bserved_ed - under observation end time 
B.a ddress, - patients address 
B.o utpatient_id - patient id 
B.t elephone, phone - patients 
B.r eg_id - registered id 
Biggest ode_no - patient number 
B.i d_card, id - 
B.b irthday, birthday - patients 
B.n ame, - the patient name 
B.n ame as patientname, - the patient name 
C.d ept_name - triage unit name 
D.r oom_name, see a doctor, department name 
E.o pera_name - founder name 
(select doctor_name from dic_organization_doctor where querytype=0 and doctor_code=a. d. octor_sn) doctor_name, - doctors name 
To_date (TO_CHAR (a.o perator_date, 'YYYY - MM - DD) +' 00:00:00 ', '- DD YYYY - MM HH24: MI: SS') yymmdd, queue date - 
ROUND (TO_NUMBER (operator_date - sysdate) * 24 * 60) as dmin, short time (points) 
Case b.s ex when '1' then 'male' when '2' then 'female' else 'pending' end sex, sex 
- cast (null as varchar (5)) as visit_flag 
, (select visit_flag from mz_visit 
Where visit_dept in (' a1201 ', 'a1202', 'a1221', 'a1222) and visit_date & gt;=bd4Hour 
And Amy polumbo _id=p_id and a.t imes=times) as visit_flag 
- cast (null as varchar (5)) as patient_type 
, (select patient_type from mz_visit 
Where visit_dept in (' a1201 ', 'a1202', 'a1221', 'a1222) and visit_date & gt;=bd4Hour 
And Amy polumbo _id=p_id and a.t imes=times) patient_type 

The from bk_emergency a 
Left the join bk_patient b on Amy polumbo _id=b.o utpatient_id 
Left the join dic_organization_dept c on a. d. ept_sn=c.d ept_code 
Left the join dic_organization_dept_room d on a.n otice_dept=d.r oom_no 
Left the join bk_users e on a.o perator=e.o pera 
Where 
A. d. ept_sn=dept_sn 
And (Anderson, send=0 or (emer_stateV='have visits and querytype=0 and a.e mer_date between bd and Ed)) 
And a.e mer_state & lt;> 'cancelled' 
And ((case when emer_state & lt;> 'hospital and observed_bd is not null then 0 else 1 end)=1) 
And 
(
(
 emer_stateV='hospital and not (observed_bd is null or observed_ed is not null)  
) 
Or 
(
Emer_stateV='has triage' 
And 
(
(querytype=0 and not (emer_date & lt; Bd or emer_date & gt; Ed)) 
Or 
(
Emer_state in (' triage ', 'have informed', 'seeing') 
And 
(
Case 
The when emer_state='has informed the and notice_date & gt; Dateadd (n - 30, getdate (), then 1 
The when emer_state='has triage and not (emer_date & lt; Bd or emer_date & gt; Ed) then 1 
The when emer_state='service in then 1 
The else 0 
End)=1 
) 
) 
) 
Or 
(
Emer_state='have visits and emer_state=' has doctor '
And 
(
(case 
The when querytype=1 and (doctor_sn=emp_sn and emer_date & gt; Convert (varchar (10), getdate () - 1121)) then 1 
The when querytype=0 then 1 
The else 0 
End)=1 
) 
) 
Or 
(a.e mer_state=emer_stateV) 
) 
) t 
Where 
(
(case when querytype=0 and emer_state='has to see a doctor and not (emer_date between bd and Ed) then 0 
The else 1 
End)=1 
) 
The order by emer_date; 


End GetEmergencyList_test; 



- SELECT TO_CHAR (sysdate, 'YYYY - MM - DD) FROM dual; 

These places error, can not use the grammar of the variable name=constant?

CodePudding user response:

Emer_stateV='hospital' and not (observed_bd is null or observed_ed is not null)
All these syntax error