Keyword Error

[iamtrying]

Hello, I have bee struggling with this statement. If take out the reference to "patternDirect" & "blockVariantBDDetail", it will run fine. The error is raising on the line with from. Any suggestions?


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

select
distinct(b."badge")||','||
a."opDate"||','||
ltrim(rtrim(a."blockRoute"))||','||
a."actDrivBegTime"||','||
a."actDrivEndTime"||','||
c."lastName"||','||
c."firstName"||','||
ltrim(rtrim(a."vehicleID"))||','||
ltrim(rtrim(a."blockID"))||','||
ltrim(rtrim(a."runNumber"))||','||
a."division"
d."patternDirection"
from "dailyPiece" a,"employeeStatus" b,"employee" c,"blockVariantBDDetail" d
where a."opDate" between sysdate-2 and sysdate-1
and a."vehicleID" is not null
and a."emp_SID"=b."emp_SID"
and b."emp_SID"=c."emp_SID";

spool off;
ERROR at line 14:
ORA-00923: FROM keyword not found where expected

 

[jimbopalmer]

I am not sure why you are doing the ||','|| but you do need the comma between columns, so
a."division"||','||
would be needed

I tried to remain child-like, all I acheived was childish.

 

[iamtrying]

jimbopalmer

||','|| is formatting and spooling the data to comma delimited. It will create a row for each record. SQLPLUS has a problem with the FROM clause.

 

[iamtrying]

Can anyone assist me with error?

 

[mdwyer]

I think there are a number of problems, so that the parser is unable to give a clear error message. What it comes up with is that the FROM clause can't be detected because the SELECT clause has invalid syntax.

First of all, the DISTINCT is not handled properly. DISTINCT applies to the entire column list, not just a single item in the list. Remove the parentheses.

Secondly, the concatenated commas (||','||) appear to come from a string manipulation in a dynamically built SQL statement. They are not formatted as item separators in the clause. Get rid of the vertical bars and single quotes.

I think you want something like this:

select distinct
b."badge"),
a."opDate",
ltrim(rtrim(a."blockRoute")),
a."actDrivBegTime",
a."actDrivEndTime",
c."lastName",
c."firstName",
ltrim(rtrim(a."vehicleID")),
ltrim(rtrim(a."blockID")),
ltrim(rtrim(a."runNumber")),
a."division", 
d."patternDirection"
from "dailyPiece" a,"employeeStatus" b,"employee" c,"blockVariantBDDetail" d
where a."opDate" between sysdate-2 and sysdate-1
and a."vehicleID" is not null
and a."emp_SID"=b."emp_SID"
and b."emp_SID"=c."emp_SID";

Then again, if the concatenated commas are a formatting string, as suggested above, you are missing a comma between items in your select list. Try this:

select distinct
b."badge"||','||
a."opDate"||','||
ltrim(rtrim(a."blockRoute"))||','||
a."actDrivBegTime"||','||
a."actDrivEndTime"||','||
c."lastName"||','||
c."firstName"||','||
ltrim(rtrim(a."vehicleID"))||','||
ltrim(rtrim(a."blockID"))||','||
ltrim(rtrim(a."runNumber"))||','||
a."division"[b], '<-- the missing comma'[/b]
d."patternDirection"
from "dailyPiece" a,"employeeStatus" b,"employee" c,"blockVariantBDDetail" d
where a."opDate" between sysdate-2 and sysdate-1
and a."vehicleID" is not null
and a."emp_SID"=b."emp_SID"
and b."emp_SID"=c."emp_SID";

 

[iamtrying]

mdwyer this statement:

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

select
distinct(b."badge")||','||
a."opDate"||','||
ltrim(rtrim(a."blockRoute"))||','||
a."actDrivBegTime"||','||
a."actDrivEndTime"||','||
c."lastName"||','||
c."firstName"||','||
ltrim(rtrim(a."vehicleID"))||','||
ltrim(rtrim(a."blockID"))||','||
ltrim(rtrim(a."runNumber"))||','||
a."division"
from "dailyPiece" a,"employeeStatus" b,"employee" c
where a."opDate" between '20041018' and '20041019'
and a."vehicleID" is not null
and a."emp_SID"=b."emp_SID"
and b."emp_SID"=c."emp_SID";

spool off;
exit
;

return the correct data. When I add the the "patternDirection" field from the "blockVariantBDDetail" table, SQLPlus just hang no error. I get the same results with the code you posted.

This is a SQL statement I ran in MicroSoft Access to get the data, but I need it to run as a job in Oracle.

SELECT DISTINCT
DBO_employeeStatus.badge,
DBO_dailyPiece.opDate,
DBO_dailyPiece.blockRoute,
DBO_dailyPiece.actDrivBegTime,
DBO_dailyPiece.actDrivEndTime,
DBO_employee.lastName,
DBO_employee.firstName,
DBO_dailyPiece.vehicleID,
DBO_dailyPiece.blockID,
DBO_dailyPiece.runNumber,
DBO_blockVariantBDDetail.patternDirection,
DBO_dailyPiece.division
FROM DBO_employee INNER JOIN ((DBO_blockVariantBDDetail INNER JOIN DBO_dailyPiece ON DBO_blockVariantBDDetail.block_SID = DBO_dailyPiece.block_SID)
INNER JOIN DBO_employeeStatus ON DBO_dailyPiece.emp_SID = DBO_employeeStatus.emp_SID) ON DBO_employee.emp_SID = DBO_employeeStatus.emp_SID
WHERE (((DBO_dailyPiece.opDate)=Date()-2) AND ((DBO_blockVariantBDDetail.patternDirection) Is Not Null));

Do you think it has something to do with the INNER JOIN?

 

[mdwyer]

SQLPlus just hang no error

This is probably because you have a Cartesian product between table 'd' and the join of tables 'a', 'b', and 'c'. Add a join condition between 'd' and one (or more) of the other tables, as you have between 'a' and 'b' and between 'b' and 'c'.

I'm not sure what your question is about running the query as a job. I don't use Microsoft's INNER JOIN syntax with Oracle, so I don't know if it's right or wrong.

 

[iamtrying]

Yes, I have been reading about Cartesian joins and that is the case here. I will add the join condition. The job I am speaking about is an Oracle Job scheduled within Oracle's Management Server.

 

[iamtrying]

Okay, I finally pushed the correct keys. This is the statement. Thanks to you all.

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

select DISTINCT
b."badge"||','||
a."opDate"||','||
ltrim(rtrim(a."blockRoute"))||','||
a."actDrivBegTime"||','||
a."actDrivEndTime"||','||
c."lastName"||','||
c."firstName"||','||
ltrim(rtrim(a."vehicleID"))||','||
ltrim(rtrim(a."blockID"))||','||
ltrim(rtrim(a."runNumber"))||','||
a."division"||','||
d."patternDirection"
from "dailyPiece" a,"employeeStatus" b,"employee" c,"blockVariantBDDetail" d
where a."opDate" between sysdate-2 and sysdate-1
and a."vehicleID" is not null
and d."patternDirection" is not null
and a."emp_SID"=b."emp_SID"
and b."emp_SID"=c."emp_SID"
and a."block_SID" =d."block_SID";

spool off;