Simple Question for a Oracle/SQL Guru

From: Jaswinder Suri <Jaswinder.Suri_at_msdw.com>
Date: Wed, 08 Dec 1999 09:26:38 +0000
Message-ID: <384E244E.C79D9B37@msdw.com>

I am a novice at writing SQL commands to manipulate an Oracle database and am having difficulty combining the following Select statements that determine -
Which patients
were seen by two Dentist during the months of August and September.

Select 1
Select Appointment.Patient_no, Patient.Patient_no, Appointment.Sv_date from Appointment, Patient
where Appointment.Patient_no = Patient.Patient_no and

          Appointment.D_name ='Miss Brown' and
          Appointment.Sv_date Between to_date('01-AUG-99') and

to_date('30-SEP-99');

Which returned the following result

Patie     Patie     Sv_Date


-----     -----     ---------

f4221 f4221 24-AUG-99
M1321 M1321 30-SEP-99 And also
Select 2
Select Appointment.Patient_no, Patient.Patient_no, Appointment.Sv_date from Appointment, Patient
where Appointment.Patient_no = Patient.Patient_no and

          Appointment.D_name ='Mr Hussein' and
          Appointment.Sv_date Between to_date('01-AUG-99') and

to_date('30-SEP-99');

Which returned the following result

Patie     Patie     Sv_Date


-----     -----     ---------


f4221    f4221   21-AUG-99

f4221 f4221 17-SEP-99
f4221 f4221 18-SEP-99

Problem is If i want to determine from the outputs which Patient saw both Dentist between
the months of August and September i would combine the select statements above, as follows

1 Select Appointment.Patient_no, Patient.Patient_no, Appointment.Sv_date
2 from Appointment, Patient
3 where Appointment.Patient_no = Patient.Patient_no and

4             Appointment.D_name ='Mr Hussein' and
5             Appointment.Sv_date Between to_date('01-AUG-99') and
6
to_date('30-SEP-99')                      IN

7          (Select Appointment.Patient_no, Patient.Patient_no,
Appointment.Sv_date
8          from Appointment, Patient
9          where Appointment.Patient_no = Patient.Patient_no and
10                  Appointment.D_name ='Miss Brown' and
11                  Appointment.Sv_date Between to_date('01-AUG-99') and

to_date('30-SEP-99'));

I keep getting an Error at line 6

           IN
           *

ORA-00933: SQL command not properly ended

Can anyone please tell me what i am doing wrong. I just can not see the mistake.

Thank you for helping,

Bye.

Jas Received on Wed Dec 08 1999 - 03:26:38 CST