Oracle SQL PLUS

Man of Honour
Joined
26 Dec 2003
Posts
31,272
Location
Shropshire
Quick question im doing an oracle assignment for uni and im at the stage where im creating the tables and doing all the validation and things and im just wondering ive got this given to me as one of my tables

Code:
PatientDrug Table
PATIENTDRUG([U]PatientNo *, DrugID *[/U], WardNo *, Units,  [U]StartDate[/U], EndDate)

PatientNo	
Forms part of the primary key, must match a value in the PATIENT table.

DrugID	
Forms part of the primary key, must match a value in the DRUG table.

WardNo	
Must show the WARD which the patient was in when the drug was administered.  Must match a value in Ward table.

Units	
A number field. Only whole numbers.

StartDate	
A date field, forms part of the primary key.

EndDate	
Must be equal to or later than the start date.

Would I need to create a new field to hold all the the primary key information or is there not actually a single primary key field like ive been used to in things like access?
 
You can just create the primary key as a composite key of the columns you need.

When you're creating your table just use your chosen columns in a primary key constraint.
 
Another quick question im trying to pull some information from to seperate tables, I need to list all records where the patient is less than 18 years old.
At the moment the query im using is
Code:
SELECT PATIENT.PATIENTNO, PATIENT.FIRSTNAME, PATIENT.SURNAME, PATIENT.DOB, DATEADMITTED FROM PATIENT, WARDPATIENT
WHERE DOB < SYSDATE - 6570;
This just seems to get the correct information from the patient table and gives the the people under 18 but when it comes to listing date admitted and ward name it gives me all of the information from the table but with the names it has pulled from patient table.
Any ideas before I cry?
 
Firstly you shouldn't use sysdate-6570 to get 18 years back from today.
Use the ADD_MONTHS function what you're doing doesn't take into account leap years so you will end up a few days out.

Secondly you're doing a cartesian join between patient and wardpatient so this is affecting your results.
Presumably you should be joining the tables on patientno?
 
Firstly you shouldn't use sysdate-6570 to get 18 years back from today.
Use the ADD_MONTHS function what you're doing doesn't take into account leap years so you will end up a few days out.
Thats a fair point I hadnt thought of that cheers
Secondly you're doing a cartesian join between patient and wardpatient so this is affecting your results.
Presumably you should be joining the tables on patientno?
Yep patientno should be the field joining them both
 
I managed to sort it by using
Code:
SELECT P.PATIENTNO, FIRSTNAME, SURNAME, DOB, DATEADMITTED, N.WARDNAME 
FROM PATIENT P, WARDPATIENT W, WARD N
WHERE ADD_MONTHS(SYSDATE,-216) < DOB AND P.PATIENTNO = W.PATIENTNO AND W.WARDNO = N.WARDNO ;
Cheers for the pointers guys
 
Back
Top Bottom