CODEWITHSUNDEEP
sql
user2901548
user2901548

Reputation: 69

SQL Error: ORA-00936: missing expression

Qns: Item Description and the treatment date of all treatments for any patients named Jessie Stange (ie GivenName is Jessie & FamilyName is Stange)

What I wrote: 

SELECT DISTINCT Description, Date as treatmentDate
WHERE doothey.Patient P, doothey.Account A, doothey.AccountLine AL, doothey.Item.I
AND P.PatientID = A.PatientID
AND A.AccountNo = AL.AccountNo
AND AL.ItemNo = I.ItemNo
AND (p.FamilyName = 'Stange' AND p.GivenName = 'Jessie');

Error:

Error at Command Line:1 Column:30
Error report:
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:

What is the missing expression?

Upvotes: 4

Views: 132210

Answers (5)

m.Lacin
m.Lacin

Reputation: 1

You didn't use FROM expression for a table

SELECT DISTINCT Description, Date as treatmentDate
**FROM <A TABLE>**
WHERE doothey.Patient P, doothey.Account A, doothey.AccountLine AL, doothey.Item.I
AND P.PatientID = A.PatientID
AND A.AccountNo = AL.AccountNo
AND AL.ItemNo = I.ItemNo
AND (p.FamilyName = 'Stange' AND p.GivenName = 'Jessie');

Upvotes: 0

Ashwini
Ashwini

Reputation: 861

In the above query when we are trying to combine two or more tables it is necessary to use joins and specify the alias name for description and date (that means, the table from which you are fetching the description and date values)

SELECT DISTINCT Description, Date as treatmentDate  
FROM doothey.Patient P  
INNER JOIN doothey.Account A ON P.PatientID = A.PatientID  
INNER JOIN doothey.AccountLine AL ON A.AccountNo = AL.AccountNo  
INNER JOIN doothey.Item I ON AL.ItemNo = I.ItemNo  
WHERE p.FamilyName = 'Stange' AND p.GivenName = 'Jessie';

Upvotes: 0

user3349710
user3349710

Reputation:

You did two mistakes . I think you misplace FROM and WHERE keywords.

SELECT DISTINCT Description, Date as treatmentDate
     FROM doothey.Patient P, doothey.Account A, doothey.AccountLine AL,  doothey.Item.I --Here you use "." operator to "I" alias 
  WHERE  -- WHERE should be located here. 
   P.PatientID = A.PatientID
    AND A.AccountNo = AL.AccountNo
    AND AL.ItemNo = I.ItemNo
    AND (p.FamilyName = 'Stange' AND p.GivenName = 'Jessie');

Upvotes: 2

user3243200
user3243200

Reputation: 1

  1  

     select ename as name,
      2  sal as salary,
      3  dept,deptno,
      4   from (TABLE_NAME or SUBQUERY)
      5   emp, emp2,  dept
      6    where
      7   emp.deptno = dept.deptno and
      8   emp2.deptno = emp.deptno
      9*  order by dept.dname

 from (TABLE_NAME or SUBQUERY)
 *
ERROR at line 4:
ORA-00936: missing expression`  select ename as name,
 sal as salary,
 dept,deptno,
  from (TABLE_NAME or SUBQUERY)
  emp, emp2,  dept
   where
  emp.deptno = dept.deptno and
  emp2.deptno = emp.deptno
  order by dept.dname`

Upvotes: -2

Nathan
Nathan

Reputation: 3200

Your statement is calling SELECT and WHERE but does not specify which TABLE or record set you would like to SELECT FROM.

SELECT DISTINCT Description, Date as treatmentDate
FROM (TABLE_NAME or SUBQUERY)<br> --This is missing from your query.
WHERE doothey.Patient P, doothey.Account A, doothey.AccountLine AL, doothey.Item.I
AND P.PatientID = A.PatientID
AND A.AccountNo = AL.AccountNo
AND AL.ItemNo = I.ItemNo
AND (p.FamilyName = 'Stange' AND p.GivenName = 'Jessie');

Upvotes: 11

Related Questions