Re: Simple Question for a Oracle/SQL Guru
Date: Wed, 08 Dec 1999 11:25:22 GMT
Message-ID: <Cer34.8556$lY5.236009_at_news.rdc1.nj.home.com>
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Your use of the "IN" operator is incorrect. "Something" has to be "IN"
e.g.=20
select ename from emp
where empno in (select empno from ...)
Your other problem is that your query selection list doesn't make sense.
If you want patients that visited BOTH doctors, then selecting the =
appointment date
is incorrect as each patient will have 2 dates.
So lets assume you want to=20
Select Appointment.Patient_no, Patient.Patient_no
from Appointment, Patient=20
where ...
The easiest way to do this is to write simple querys that select for =
each doctor and
then to combine them using an intersect operator.
If this is a homework question, then shame on you for cheating, but the =
INTERSECT solution
should be enough to satisfy any academic.
If this is for real then you should use a correlated subquery with an =
exists operator, but if your class
training hasn't taught you how to use an IN operator, then you might =
want to hold of on correlated subqueries
for a few weeks.
In any case, get a good SQL book, I suggest Oracle, the Complete =
Reference from Oracle Press, and look
CLOSELY at the syntax.
--=20
Paul Dorsey
Dulcian, Inc.
(212) 595-7223
web address: http://www.dulcian.com
email: pdorsey_at_dulcian.com
Jaswinder Suri <Jaswinder.Suri_at_msdw.com> wrote in message = news:384E23A4.8006F298_at_msdw.com...
=20
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=20
were seen by two Dentist during the months of August and September.=20
Select 1=20
Select Appointment.Patient_no, Patient.Patient_no, Appointment.Sv_date =
from Appointment, Patient=20
where Appointment.Patient_no =3D Patient.Patient_no and=20
Appointment.D_name =3D'Miss Brown' and=20
Appointment.Sv_date Between to_date('01-AUG-99') and=20
=
to_date('30-SEP-99');=20
Which returned the following result=20
Patie Patie Sv_Date=20 ----- ----- ---------=20
f4221 f4221 24-AUG-99=20
M1321 M1321 30-SEP-99=20 And also=20
Select 2=20
Select Appointment.Patient_no, Patient.Patient_no, Appointment.Sv_date =
from Appointment, Patient=20
where Appointment.Patient_no =3D Patient.Patient_no and=20
Appointment.D_name =3D'Mr Hussein' and=20
Appointment.Sv_date Between to_date('01-AUG-99') and=20
=
to_date('30-SEP-99');=20
Which returned the following result=20
Patie Patie Sv_Date=20 ----- ----- ---------=20 f4221 f4221 21-AUG-99=20
f4221 f4221 17-SEP-99=20
f4221 f4221 18-SEP-99=20
Problem is If i want to determine from the outputs which Patient saw =
both Dentist between=20
the months of August and September i would combine the select =
statements above, as follows=20
1 Select Appointment.Patient_no, Patient.Patient_no, =
Appointment.Sv_date=20
2 from Appointment, Patient=20
3 where Appointment.Patient_no =3D Patient.Patient_no and=20
4 Appointment.D_name =3D'Mr Hussein' and=20
5 Appointment.Sv_date Between to_date('01-AUG-99') and=20
6 =
to_date('30-SEP-99') IN=20
7 (Select Appointment.Patient_no, Patient.Patient_no, =
Appointment.Sv_date=20
8 from Appointment, Patient=20
9 where Appointment.Patient_no =3D Patient.Patient_no and=20
10 Appointment.D_name =3D'Miss Brown' and=20
11 Appointment.Sv_date Between to_date('01-AUG-99') =
and=20
=
to_date('30-SEP-99'));=20
I keep getting an Error at line 6=20
IN=20
*=20
ORA-00933: SQL command not properly ended=20
Can anyone please tell me what i am doing wrong. I just can not see = the mistake.=20
Thank you for helping,=20
Bye.=20
Jas=20
=20
=20
=20
------=_NextPart_000_0025_01BF4142.E81E2580
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3D"text/html; charset=3Diso-8859-1" =http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2614.3500" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT size=3D2>Your use of the "IN" operator is =incorrect.</FONT></DIV>
<DIV><FONT size=3D2>"Something" has to be "IN"</FONT></DIV> <DIV> </DIV> <DIV><FONT size=3D2>e.g. </FONT></DIV> <DIV><FONT size=3D2>select ename from emp</FONT></DIV> <DIV><FONT size=3D2>where empno in (select empno from ...)</FONT></DIV> <DIV> </DIV> <DIV><FONT size=3D2>Your other problem is that your query selection list =doesn't=20
make sense.</FONT></DIV>
<DIV><FONT size=3D2>If you want patients that visited BOTH doctors, then = selecting=20
the appointment date</FONT></DIV>
<DIV><FONT size=3D2>is incorrect as each patient will have 2 = dates.</FONT></DIV>
<DIV> </DIV> <DIV><FONT size=3D2>So lets assume you want to </FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>Select Appointment.Patient_no, =Patient.Patient_no<BR>from=20
Appointment, Patient </FONT></DIV>
<DIV><FONT size=3D2>where ...</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>The easiest way to do this is to write simple querys =that=20
select for each doctor and</FONT></DIV>
<DIV><FONT size=3D2>then to combine them using an intersect = operator.</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>If this is a homework question, then shame on you = for=20
cheating, but the INTERSECT solution</FONT></DIV> <DIV><FONT size=3D2>should be enough to satisfy any = academic.</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>If this is for real then you should use a correlated = subquery=20
with an exists operator, but if your class</FONT></DIV> <DIV><FONT size=3D2>training hasn't taught you how to use an IN = operator, then you=20
might want to hold of on correlated subqueries</FONT></DIV>
<DIV><FONT size=3D2>for a few weeks.</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>In any case, get a good SQL book, I suggest Oracle, =the=20
Complete Reference from Oracle Press, and look</FONT></DIV> <DIV><FONT size=3D2>CLOSELY at the syntax.</FONT></DIV> <DIV><BR><FONT size=3D2>-- <BR>Paul Dorsey<BR>Dulcian, Inc.<BR>(212)=20 595-7223<BR>web address: <A=20
href=3D"http://www.dulcian.com">http://www.dulcian.com</A><BR>email: <A=20 href=3D"mailto:pdorsey_at_dulcian.com">pdorsey_at_dulcian.com</A><BR></FONT></D= IV>
<DIV>Jaswinder Suri <<A=20
href=3D"mailto:Jaswinder.Suri_at_msdw.com">Jaswinder.Suri_at_msdw.com</A>> = wrote in=20
message <A=20
href=3D"news:384E23A4.8006F298_at_msdw.com">news:384E23A4.8006F298_at_msdw.com<= /A>...</DIV>
<BLOCKQUOTE=20
style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: = 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px"> =20 <BR>I am a novice at writing SQL commands to manipulate an Oracle = database and=20
am having difficulty combining the following Select statements that = determine=20
- Which patients <BR>were seen by two Dentist during the months of = August and=20 September.=20 [Quoted] <P>Select 1 <BR>Select Appointment.Patient_no, Patient.Patient_no,=20 Appointment.Sv_date <BR>from Appointment, Patient <BR>where=20 Appointment.Patient_no =3D Patient.Patient_no and=20 <BR> = Appointment.D_name=20 =3D'<FONT color=3D#ff0000>Miss Brown</FONT>' and=20 <BR> = Appointment.Sv_date=20 Between to_date('01-AUG-99') and=20 = <BR> &nb= sp; &nbs= p;  = ; = &= nbsp; =20 to_date('30-SEP-99');=20 <P>Which returned the following result=20 <P>Patie Patie Sv_Date =
<BR>----- ----- =
---------=20
[Quoted] <BR>f4221 f4221 24-AUG-99 <BR>M1321 =
M1321=20
30-SEP-99=20
<P>And also <BR>Select 2 <BR>Select Appointment.Patient_no,=20
Patient.Patient_no, Appointment.Sv_date <BR>from Appointment, Patient=20
<BR>where Appointment.Patient_no =3D Patient.Patient_no and=20
<BR> =
Appointment.D_name=20
=3D'<FONT color=3D#ff0000>Mr Hussein</FONT>' and=20
<BR> =
Appointment.Sv_date=20
Between to_date('01-AUG-99') and=20
=
<BR> &nb= sp; &nbs= p;  = ; = &=nbsp; =20
to_date('30-SEP-99');=20
<P>Which returned the following result=20 <P>Patie Patie Sv_Date =
<BR>----- ----- = ---------=20
<BR>f4221 f4221 21-AUG-99=20 <BR>f4221 f4221 17-SEP-99=20 <BR>f4221 f4221 18-SEP-99=20 <P>Problem is If i want to determine from the outputs which Patient =saw both=20
Dentist between <BR>the months of August and September i would combine = the=20
select statements above, as follows=20 [Quoted] <P>1 Select Appointment.Patient_no, Patient.Patient_no,=20 Appointment.Sv_date <BR>2 from Appointment, Patient=20 <BR>3 where Appointment.Patient_no =3D Patient.Patient_no = and=20
=
<BR>4 &n= bsp;=20
Appointment.D_name =3D'<FONT color=3D#ff6666>Mr Hussein</FONT>' and=20 =
<BR>5 &n= bsp;=20
Appointment.Sv_date Between to_date('01-AUG-99') and=20 =
<BR>6 &n= bsp; &nb= sp; &nbs= p;  = ; = =20
=
to_date('30-SEP-99')  = ; = =20
<FONT color=3D#ff6666>IN</FONT>=20
<P>7 (Select=20 Appointment.Patient_no, Patient.Patient_no, Appointment.Sv_date=20 <BR>8 from = Appointment,=20
Patient <BR>9 = where=20
Appointment.Patient_no =3D Patient.Patient_no and=20 =
<BR>10 &= nbsp; =20
Appointment.D_name =3D'<FONT color=3D#ff6666>Miss Brown</FONT>' and=20 =
<BR>11 &= nbsp; =20
Appointment.Sv_date Between to_date('01-AUG-99') and=20 =
<BR> &nb= sp; &nbs= p;  = ; = &= nbsp; &n=bsp; =20
to_date('30-SEP-99'));=20
<P>I keep getting an Error at line 6=20 <BR> IN=20 <BR> *=20 <BR>ORA-00933: SQL command not properly ended=20 <P>Can anyone please tell me what i am doing wrong. I just can =not see=20
the mistake.=20
<P>Thank you for helping,=20 <P>Bye.=20 <P>Jas <BR> <BR> <BR> =</P></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_0025_01BF4142.E81E2580-- Received on Wed Dec 08 1999 - 12:25:22 CET