Missing Expression Error [message #7080] |
Tue, 20 May 2003 08:00 |
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 |
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 |
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 #7094 is a reply to message #7093] |
Tue, 20 May 2003 15:43 |
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 |
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 |
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 #7116 is a reply to message #7115] |
Wed, 21 May 2003 15:23 |
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.
|
|
|