| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Really slow SQL query - Pleas help a newbie before i get cremated on the bonfire tomorrow!
Hi i am new to SQL and PL SQL.
The database we use for our occupational health department is Oracle 8.1.7.5.
There is a View called CASS.OPASRESTRICTIONS_VIEW
the table data is held as follows
Name Null? Type ----------------------------------------- -------- ----------------------- PIN NOT NULL VARCHAR2(10) SID VARCHAR2(15) NI VARCHAR2(10) SURNAME NOT NULL VARCHAR2(40) FORENAMES NOT NULL VARCHAR2(40) DOB NOT NULL DATE GENDER NOT NULL NUMBER(38) TITLE NUMBER(38) ADDRESS1 VARCHAR2(50) ADDRESS2 VARCHAR2(50) ADDRESS3 VARCHAR2(50) ADDRESS4 VARCHAR2(50) POSTCODE VARCHAR2(15) HOMETELEPHONE VARCHAR2(20) WORKTELENO VARCHAR2(20) WORKEMAIL VARCHAR2(50) NATIONALITY NUMBER(38) ETHNICORIGIN NUMBER(38) OCCUPATION NUMBER(38) EMPLOYMENTSTATUS NUMBER(38) AREAOFWORKCODE VARCHAR2(10) TYPEOFWORK NUMBER(38) BUSINESSUNITCODE VARCHAR2(10) SHIFT NUMBER(38) SUPERVISOR VARCHAR2(30) SUPERVISORPIN VARCHAR2(10) SUPERVISOREMAIL VARCHAR2(50) DEPARTMENTCODE VARCHAR2(10) NOKNAME VARCHAR2(50) NOKADDRESS1 VARCHAR2(50) NOKADDRESS2 VARCHAR2(50) NOKADDRESS3 VARCHAR2(50) NOKADDRESS4 VARCHAR2(50) NOKPOSTCODE VARCHAR2(15) NOKRELATIONSHIP NUMBER(38) NOKTELEPHONENUMBER VARCHAR2(20) NOTES VARCHAR2(2000) COSTCENTRE VARCHAR2(10) FULLNAME VARCHAR2(82) L_P__PIN NOT NULL VARCHAR2(10) L_P__NI_NUMBER VARCHAR2(10) L_P__SECONDARY_IN VARCHAR2(15) L_P__SURNAME NOT NULL VARCHAR2(40) P__PREVIOUS_SURNAME VARCHAR2(40) L_P__FORENAMES NOT NULL VARCHAR2(40) D_L_P__DATE_OF_BIRTH NOT NULL DATE P__GENDER VARCHAR2(255) P__TITLE VARCHAR2(255) P__ADDRESS_1 VARCHAR2(50) P__ADDRESS_2 VARCHAR2(50) P__TOWN VARCHAR2(50) P__COUNTY VARCHAR2(50) P__BUSINESS_UNIT VARCHAR2(255) P__DEPARTMENT VARCHAR2(255) P__NATIONALITY VARCHAR2(255) P__ETHNIC_ORIGIN VARCHAR2(255) P__AREA_OF_WORK VARCHAR2(255) P__ZONE VARCHAR2(255) P__OCCUPATION VARCHAR2(255) P__SHIFT VARCHAR2(255) P__TYPE_OF_WORK VARCHAR2(255) P__EMPLOYMENT_STATUS VARCHAR2(255) P__NOK_RELATIONSHIP VARCHAR2(255) DATEOFRECALL DATE TIMEOFRECALL DATE STARTDATE DATE FINISHDATE DATE EPISODETYPE NUMBER(38) BUSINESSUNIT NUMBER(38) DEPARTMENT NUMBER(38) AREAOFWORK NUMBER(38) ZONE NUMBER(38) S__BUSINESS_UNIT VARCHAR2(255) S__DEPARTMENT VARCHAR2(255) S__AREA_OF_WORK VARCHAR2(255) S__ZONE VARCHAR2(255) D_S__DATE_OF_RECALL DATE T_S__TIME_OF_RECALL DATE S__EPISODE_TYPE VARCHAR2(255) D_S__EPISODE_START_DATE DATE D_S__EPISODE_FINISH_DATE DATE EPISODEUNIQUEKEY VARCHAR2(30) APPSUNIQUEKEY VARCHAR2(30) D_S__APPOINTMENT_DATE DATE T_S__APPOINTMENT_TIME DATE TIMEIN DATE TIMEOUT DATE EVENTTYPE VARCHAR2(50) DUEDATE DATE ATTENDANCEDATE DATE SEENBY NUMBER(38) OUTCOME NUMBER(38) OHLOCATION NUMBER(38) EXTERNAL_REFERRAL NUMBER(38) EXT_REFERRAL_DATE DATE COMMENTS VARCHAR2(2000) USERA NUMBER(38) USERC NUMBER(38) DATEA VARCHAR2(19) DATEC VARCHAR2(19) COST NUMBER(8,2) D_S__ATTENDANCE_DATE DATE S__EVENT_TYPE VARCHAR2(50) D_S__DUE_DATE DATE T_S__TIME_IN DATE T_S__TIME_OUT DATE S__SEEN_BY VARCHAR2(255) S__OUTCOME VARCHAR2(255) S__OH_LOCATION VARCHAR2(255) S__EXTERNAL_REFERRAL VARCHAR2(255) D_S__EXTERNAL_REFERRAL_DATE DATE S__COMMENTS VARCHAR2(2000) RESTRICTIONUNIQUEKEY VARCHAR2(30)
The s__event_type field holds the restriction description e.g.
Restriction applied: Special Needs
Restriction lifted: Lifting and handling
Pin is Payroll Number 534473 (always six digits) and not null
I have created a concatonated field using PIN and S__Event type to include in a self join this is because there is no other fields that i can join by to produce the correct result
I am running the following code on from my Networked PC.
a.FULLNAME,
A.PIN||trim(' 'FROM SUBSTR( a.S__EVENT_TYPE, 21)),
a.PIN,
FROM CASS.OPASRESTRICTIONS_VIEW A
WHERE
NOT EXISTS (SELECT DISTINCT B.PIN||trim(' ' FROM SUBSTR(
B.S__EVENT_TYPE, 21)) FROM CASS.OPASRESTRICTIONS_VIEW B WHERE B.pin
<>'1' AND
B.EVENTTYPE LIKE 'Rest%' AND
A.PIN||trim(' ' FROM SUBSTR( a.S__EVENT_TYPE, 21)) = B.PIN||trim('
' FROM SUBSTR( B.S__EVENT_TYPE, 21)) AND
trim(' ' FROM SUBSTR(B.EVENTTYPE,13,1)) LIKE 'L') AND
pin <>'1' AND
a.EVENTTYPE LIKE 'Rest%';
The code above is used to select all the applied restrictions from the View and look up using a not exists statement in the where clause so that it only displays restrictions that are applied but not lifted
Is there an easier way of doing this?
I have looked up recordsets and the possibility of using an IF-THEN-ELSE but i don't really understand how to use them. I assume i need two one for applied and one for lifted restrictions.
Any help at all is appreciated as i'm on my own on this one!!!
Many thanks
Martin
Received on Tue Nov 04 2003 - 07:37:37 CST
 |
 |