Re: Simple Question for a Oracle/SQL Guru
Date: Wed, 08 Dec 1999 23:59:45 GMT
Message-ID: <82mrdf$146$1_at_nnrp1.deja.com>
I'm not sure I fully understand what you are trying to do, but with respect to the structure of your combined SQL statement you are missing the following:
Appointment.Sv_date Between to_date('01-AUG-99') and to_date('30-SEP-99')
AND (Appointment.Patient_no, Patient.Patient_no,
> Appointment.Sv_date) 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'));
You need to specify the set of variables that you are testing the "in" against (a.patient_no, b.patient_no, c.sv_date).
That should get rid of the error.
Randy Meisner
Senior Consultant
ROMAC Emerging Technologies
In article <384E23A4.8006F298_at_msdw.com>,
Jaswinder.Suri_at_msdw.com wrote:
>
> --------------06EAE59262A2DD5E87C55DDD
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> 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
>
> --------------06EAE59262A2DD5E87C55DDD
> Content-Type: text/html; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML>
>
> <BR>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
> <BR>were seen by two Dentist during the months of August and
September.
> <P>Select 1
> <BR>Select Appointment.Patient_no, Patient.Patient_no,
Appointment.Sv_date
> <BR>from Appointment, Patient
> <BR>where Appointment.Patient_no = Patient.Patient_no and
> <BR>
Appointment.D_name
> ='<FONT COLOR="#FF0000">Miss Brown</FONT>' and
> <BR>
Appointment.Sv_date
> Between to_date('01-AUG-99') and
>
<BR> &n bsp; &n bsp; &n bsp; &n bsp; &n bsp;
> to_date('30-SEP-99');
> <P>Which returned the following result
> <P>Patie Patie Sv_Date
> <BR>----- -----
> <BR>f4221 f4221 24-AUG-99
> <BR>M1321 M1321 30-SEP-99
> <P>And also
> <BR>Select 2
> <BR>Select Appointment.Patient_no, Patient.Patient_no,
Appointment.Sv_date
> <BR>from Appointment, Patient
> <BR>where Appointment.Patient_no = Patient.Patient_no and
> <BR>
Appointment.D_name
> ='<FONT COLOR="#FF0000">Mr Hussein</FONT>' and
> <BR>
Appointment.Sv_date
> Between to_date('01-AUG-99') and
>
<BR> &n bsp; &n bsp; &n bsp; &n bsp; &n bsp;
> to_date('30-SEP-99');
> <P>Which returned the following result
> <P>Patie Patie Sv_Date
> <BR>----- -----
> <BR>f4221 f4221 21-AUG-99
> <BR>f4221 f4221 17-SEP-99
> <BR>f4221 f4221 18-SEP-99
> <P>Problem is If i want to determine from the outputs which Patient
saw
> both Dentist between
> <BR>the months of August and September i would combine the select
statements
> above, as follows
> <P>1 Select Appointment.Patient_no, Patient.Patient_no,
Appointment.Sv_date
> <BR>2 from Appointment, Patient
> <BR>3 where Appointment.Patient_no = Patient.Patient_no
and
>
<BR>4 & nbsp;
> Appointment.D_name ='<FONT COLOR="#FF6666">Mr Hussein</FONT>' and
>
<BR>5 & nbsp;
> Appointment.Sv_date Between to_date('01-AUG-99') and
>
<BR>6 & nbsp; & nbsp; & nbsp; & nbsp; & nbsp;
>
to_date('30-SEP-99') &nbs p; &nbs p;
> <FONT COLOR="#FF6666">IN</FONT>
> <P>7 (Select
Appointment.Patient_no,
> Patient.Patient_no, Appointment.Sv_date
> <BR>8 from
Appointment,
> Patient
> <BR>9 where
Appointment.Patient_no
> = Patient.Patient_no and
>
<BR>10
> Appointment.D_name ='<FONT COLOR="#FF6666">Miss Brown</FONT>' and
>
<BR>11
> Appointment.Sv_date Between to_date('01-AUG-99') and
>
<BR> &n bsp; &n bsp; &n bsp; &n bsp; &n bsp; &n bsp;
> to_date('30-SEP-99'));
> <P>I keep getting an Error at line 6
> <BR> IN
> <BR> *
> <BR>ORA-00933: SQL command not properly ended
> <P>Can anyone please tell me what i am doing wrong. I just can
not
> see the mistake.
> <P>Thank you for helping,
> <P>Bye.
> <P>Jas
> <BR>
> <BR>
> <BR> </HTML>
>
> --------------06EAE59262A2DD5E87C55DDD--
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Dec 09 1999 - 00:59:45 CET