[sql] 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?

This question is related to sql

The answer is


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');

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';

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');

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');

  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`