Can somebody pls help me..... I'm stuck on question 4. don't know how to compare..... the question is: 4. The data stored about admissions is sometimes entered incorrectly in that admission and discharge dates are swapped about. Determine whether there are any examples of this in the current database content, and if so, reverse the two dates. can somebody pls help......... ah, 20 questions all together. and I'm just in question 4................... CREATE TABLE admission( patientID char(4), admissionDate date, dischargeDate date, reason char(1), wardNumber char(2), bedNumber number, constraint admission_pk primary key (patientID, admissionDate), constraint admission_bed_fk foreign key (wardNumber, bedNumber) references bed (wardNumber, bedNumber)); INSERT INTO admission VALUES('1234','24-JAN-04','29-JAN-04','H','B1',3); INSERT INTO admission VALUES('1234','21-SEP-04','29-SEP-04','H','B1',4); INSERT INTO admission VALUES('1234','24-JAN-05','28-JAN-05','N','B1',3); INSERT INTO admission VALUES('1491','24-JAN-04','31-JAN-04','N','B1',2); INSERT INTO admission VALUES('2378','22-SEP-04','27-SEP-04','H','B1',1); INSERT INTO admission VALUES('1491','24-JAN-05','03-FEB-05','N','B1',4); INSERT INTO admission VALUES('2386','22-DEC-04','25-DEC-04','C','B1',3); INSERT INTO admission VALUES('2378','25-DEC-04','28-DEC-04','C','B1',2); INSERT INTO admission VALUES('1387','21-DEC-04',null,null,'B2',1); INSERT INTO admission VALUES('1491','24-FEB-05','22-FEB-05','C','B1',4); INSERT INTO admission VALUES('1234','1-MAR-05',null,null,'B2',3); INSERT INTO admission VALUES('2378','2-MAR-05',null,null,'A1',1);

I think that it's so easy.Maybe you can use code like this:

UPDATE admission
   SET admissionDate = dischargeDate,
       dischargeDate = admissionDate
 WHERE NVL(dischargeDate,SYSDATE) - admissionDate < 0;

You can try it.

PROMPT ---------------------------------------------------------------------------; PROMPT "Reverse the Admission and Discharge dates into order" PROMPT ---------------------------------------------------------------------------; PROMPT "Check the date of the table" SELECT patientID, admissionDate, dischargeDate FROM admission; PROMPT ---------------------------------------------------------------------------; PROMPT "Test the incorrect entry" select patientID,admissionDate, dischargeDate from admission where admissionDate >= dischargeDate; PROMPT ---------------------------------------------------------------------------; PROMPT "REVERSE THE DAY" UPDATE admission SET admissionDate = dischargeDate, dischargeDate = admissionDate WHERE NVL(dischargeDate,SYSDATE ) - admissionDate < 0; I've tried, but doesn't work................. what should I do?

Hi,    I think you should not do that:    UPDATE admission    SET admissionDate = dischargeDate,        dischargeDate = admissionDate  WHERE NVL(dischargeDate,SYSDATE) - admissionDate < 0;    The contents of admissionDate were updated while you firstly made "SET admissionDate = dischargeDate,". After this had been done, the original contents of admissionDate were lost.    In my opinions, You could try the following:    1.Alter Table admission Add TempDate Date; 2.Update admission SET TempDate=dischargeDate; 3.Update admission SET TempDate=admissionDate,   admissionDate=dischargeDate   Where dischargeDate

Sorry for typing mistakes: 1.Alter Table admission Add TempDate Date; 2.Update admission SET TempDate=dischargeDate; 3.Update admission SET TempDate=admissionDate, admissionDate=dischargeDate Where dischargeDate < admissionDate; 4.Update admission SET dischargeDate=TempDate; 5.Alter Table admission Drop TempDate; Good luck! wct

引言: I've tried, but doesn't work................. what should I do?

N............ Could you tell me what's kind of database you used?

it works now ! thx for u guys, really kind! thx again