Missing Expression

[iamtrying]

Hello Everyone,

I am having a problem getting this sql statement to run in oracle. It ran fine for one year until today. I am trying to retrieve data from the prior day. I just can't figure it out. Thanks


SQL> select
2 distinct
3 (a."opDate")||','||
4 ltrim(rtrim(a."vehicleID"))||','||
5 ltrim(rtrim(a."runNumber"))||','||
6 ltrim(rtrim(b."patternDirection"))||','||
7 from "dailyPiece" a, "blockVariantBDDetail" b
8 where a."opDate" between 'sysdate-1'
9 and a."vehicleID" is not null
10 and b."patternDirection" is not null;
from "dailyPiece" a, "blockVariantBDDetail" b
*
ERROR at line 7:
ORA-00936: missing expression

 

[Dagon]

This bit:

ltrim(rtrim(b."patternDirection"))||','||
from

You have a terminating || before the from, so Oracle is expecting something there.

 

[iamtrying]

Dagon,

Are you saying I should remove the terminators before from?

 

[Dagon]

Yes..or put another expression after the last "||".

 

[iamtrying]

When I remove the terminators here is what happens

SQL> select
2 distinct
3 (a."opDate")||','||
4 ltrim(rtrim(a."vehicleID"))||','||
5 ltrim(rtrim(a."runNumber"))||','||
6 ltrim(rtrim(b."patternDirection"))
7 from "dailyPiece" a, "blockVariantBDDetail" b
8 where a."opDate" between 'sysdate-1'
9 and a."vehicleID" is not null
10 and b."patternDirection" is not null;
and b."patternDirection" is not null
*
ERROR at line 10:
ORA-00933: SQL command not properly ended

 

[Dagon]

The problem is now with your "between" statement:

where a."opDate" between 'sysdate-1'

There are 2 problems with this. "Between" needs 2 parameters, so you need to say between 'sysdate-1' and something else e.g. sysdate. Assuming you actually mean yesterday's date rather than the literal string 'sysdate-1', you should also not put quotes round this expression. Also, bear in mind sysdate includes a time portion as well as just the date - sysdate-1 is today's date and time minus 1 day.

Your expression should perhaps be something like:

where a."opDate" between trunc(sysdate-1) and trunc(sysdate)

I also notice you have no join conditions on tables dailyPiece and blockVariantBDDetail.

 

[iamtrying]

Dagon,

Here is what I did to get some good results

set pagesize 0;
set heading off;
set trimspool on;
set feedback off;
alter session set nls_date_format='YYYYMMDD';
spool C:\Backups\CIN\DailyPiece_RTO.log

SELECT DISTINCT
dailyPiece."opDate", dailyPiece."runNumber", dailyPiece."vehicleID",
blockVariantBDDetail."patternDirection"
FROM
"DBO"."dailyPiece" dailyPiece,
"DBO"."blockVariantBDDetail" blockVariantBDDetail
WHERE
dailyPiece."block_SID" = blockVariantBDDetail."block_SID" AND
(blockVariantBDDetail."patternDirection" = 'COUNTERCLO' OR
blockVariantBDDetail."patternDirection" = 'EAST' OR
blockVariantBDDetail."patternDirection" = 'NORTH' OR
blockVariantBDDetail."patternDirection" = 'SOUTH' OR
blockVariantBDDetail."patternDirection" = 'WEST') AND
dailyPiece."opDate" between sysdate-2 and sysdate -1 ;

It seems to returning the correct data

Thank you

 

[SantaMufasa]

Another issue to think about: Using double quotes (") to surround your table names and column names complicates your life. It means that you must ALWAYS use double quotes to refer to your named objects (if you have even one non-uppercase or special character in the name).

If, instead, you create your table names and column names with a leading alphabetic character, with numerals, and/or with underscore, then you never need double quotes to offset the name(s) and you can refer to the named objects in any case you please, regardless of how you originally named the objects.

Let us know if this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[iamtrying]

It is helpful.

Unfortunately, the tables were created by an outside vendor and the application is proprietary. The tablenames exist in the database as you see them in the statement. They even have double quotes around each column name. What a pita.