Home » SQL & PL/SQL » SQL & PL/SQL » Missing Expression Error
Missing Expression Error [message #7080] Tue, 20 May 2003 08:00 Go to next message
Ray
Messages: 58
Registered: January 2000
Member
Hi, I am a newbie to SQL

I get this error:

==> SQLException:
Message: ORA-00936: missing expression

SQLState: 42000
ErrorCode: 936

when trying to execute the statement below. Please let me know if you need more information. Any suggestions would be appreciated. Thanks.

SELECT METRIX_PROJECT_TASK.REQUEST_ID, METRIX_PROJECT_TASK.PROJ_TASK_ID, METRIX_PROJECT_TASK.EXPORT_TASK, Left([[METRIX_REQUEST]].[[PLACE_ID]],2) AS OpCo, METRIX_PERSON.FIRST_NAME FROM METRIX_PERSON INNER JOIN (METRIX_PROJECT_TASK INNER JOIN METRIX_REQUEST ON METRIX_PROJECT_TASK.REQUEST_ID = METRIX_REQUEST.REQUEST_ID) ON METRIX_PERSON.PERSON_ID = METRIX_PROJECT_TASK.PERSON_ID WHERE (((METRIX_PROJECT_TASK.EXPORT_TASK) Is Null) AND ((Left([[METRIX_REQUEST]].[[PLACE_ID]],2))=25) AND ((METRIX_PERSON.FIRST_NAME)="GLVND"));
Re: Missing Expression Error [message #7082 is a reply to message #7080] Tue, 20 May 2003 08:50 Go to previous messageGo to next message
Martin Chadderton
Messages: 35
Registered: May 2003
Member
Hmm... looks like an Access or (put microsoft product here) SQL this, basically, your problem is the Left function, doesn't exist in oracle, i..e

SQL> select left([[dummy]], 2) from dual;
select left([[dummy]], 2) from dual
*
ERROR at line 1:
ORA-00936: missing expression

The Oracle alternative is

SQL> SELECT SUBSTR('XYZ', 1, 2) FROM DUAL;

SU
--
XY

You will also have problems with the double quotes, i.e.

SQL> SELECT "xyz" from dual;
SELECT "xyz" from dual
*
ERROR at line 1:
ORA-00904: invalid column name

Oracle uses single quotes

Regards.
Re: Missing Expression Error [message #7083 is a reply to message #7082] Tue, 20 May 2003 09:04 Go to previous messageGo to next message
Ray
Messages: 58
Registered: January 2000
Member
Martin,

Thanks for your suggestion, but after replacing left with substr and putting single quotes, I still get same error message. Any other ideas? Thanks. This is what I have now:

SELECT METRIX_PROJECT_TASK.REQUEST_ID, METRIX_PROJECT_TASK.PROJ_TASK_ID, METRIX_PROJECT_TASK.EXPORT_TASK, SUBSTR([[METRIX_REQUEST]].[[PLACE_ID]],2) AS OpCo, METRIX_PERSON.FIRST_NAME FROM METRIX_PERSON INNER JOIN (METRIX_PROJECT_TASK INNER JOIN METRIX_REQUEST ON METRIX_PROJECT_TASK.REQUEST_ID = METRIX_REQUEST.REQUEST_ID) ON METRIX_PERSON.PERSON_ID = METRIX_PROJECT_TASK.PERSON_ID WHERE (((METRIX_PROJECT_TASK.EXPORT_TASK) Is Null) AND ((SUBSTR([[METRIX_REQUEST]].[[PLACE_ID]],2))=25) AND ((METRIX_PERSON.FIRST_NAME)='GLVND'));
Re: Missing Expression Error [message #7089 is a reply to message #7083] Tue, 20 May 2003 12:40 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You also need to remove the square brackets - [[]].
Re: Missing Expression Error [message #7091 is a reply to message #7089] Tue, 20 May 2003 13:31 Go to previous messageGo to next message
Ray
Messages: 58
Registered: January 2000
Member
Thanks, Todd, that error went away but now I get the error that the SQL command was not properly ended.
Re: Missing Expression Error [message #7092 is a reply to message #7091] Tue, 20 May 2003 13:42 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You are trying to run this on Oracle 9i, correct? The INNER JOIN syntax is not valid on 8i or earlier.
Re: Missing Expression Error [message #7093 is a reply to message #7092] Tue, 20 May 2003 14:16 Go to previous messageGo to next message
Ray
Messages: 58
Registered: January 2000
Member
It's version 8.174. What would be valid instead of INNER JOIN? Thanks.
Re: Missing Expression Error [message #7094 is a reply to message #7093] Tue, 20 May 2003 15:43 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
select pt.request_id,
       pt.proj_task_id,
       pt.export_task,
       substr(r.place_id, 1, 2) as opco,
       p.first_name
  from metrix_person p,
       metrix_project_task pt,
       metrix_request r
 where p.first_name = 'GLVND'
   and pt.person_id = p.person_id
   and pt.export_task is null
   and r.request_id = pt.request_id 
   and substr(r.place_id, 1, 2) = '25';


Note that the joins are handled in the WHERE clause and that the LEFT(<value>, 2) call is replaced by SUBSTR(<value>, 1, 2). I also used aliases to make the column references more readable.
Re: Missing Expression Error [message #7108 is a reply to message #7094] Wed, 21 May 2003 07:42 Go to previous messageGo to next message
Ray
Messages: 58
Registered: January 2000
Member
I'm getting a "ORA-00911: invalid character" error now. I copied your code exactly. Also, are you referring "25" here as a string? I think its meant to be referred as a integer. Sorry, I am new to SQL. Thanks.
Re: Missing Expression Error [message #7112 is a reply to message #7108] Wed, 21 May 2003 13:47 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I don't see anything that would cause the ORA-00911 error. Do you get this error when running the query from SQL*Plus?

The '25' is a string because the SUBSTR function returns a string. You could also do:

to_number(substr(r.place_id, 1, 2)) = 25


Oracle will do implicit conversions for you, but it is a good idea to be explicit with datatype conversions so there is no confusion. I am assuming here that the datatype of the place_id column is VARCHAR2.
Re: Missing Expression Error [message #7114 is a reply to message #7112] Wed, 21 May 2003 14:10 Go to previous messageGo to next message
Ray
Messages: 58
Registered: January 2000
Member
I don't have sql plus. I am running this from my Java program and making a jdbc connection to an Oracle server 8
Re: Missing Expression Error [message #7115 is a reply to message #7114] Wed, 21 May 2003 15:11 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Is it possible it is complaining about the semicolon at the end?
Re: Missing Expression Error [message #7116 is a reply to message #7115] Wed, 21 May 2003 15:23 Go to previous message
Ray
Messages: 58
Registered: January 2000
Member
Yup. that what it is--i always thought that you have to end the sql with that until a dba here told me to use just get rid of it. Thanks. Now I'm getting a table or view does not exist-LOL(the errors just keep comin') which I guess is something I have to solve on my own.
Previous Topic: Result set of this format
Next Topic: Retrieving data about a table (e.g., column names)
Goto Forum:
  


Current Time: Thu Mar 28 16:40:49 CDT 2024