ORA-00923: FROM keyword not found where expected using shell script

Question

there are so many post about this error. However i am not able to resolve my code. i am trying to run this SQl in shell script, but it is giving me this error. ORA-00923: FROM keyword not found where expected

SELECT
  wrk.WO_ID,
  srq.CSDL_SEQ_NO,
  srq.ASDL_CMD,
  srq.HOST_CLLI                                    AS NEP,
  MIN(srq.START_DTS)                               AS start_dt,
  MAX(srq.COMP_DTS)                                AS comp_dt,
  ((MAX(srq.COMP_DTS)-MIN(srq.START_DTS)) * 86400) AS proc_time
FROM
  SARMPRD1.TBL_ASDL_LOG srq,
  sarmprd1.tbl_wrk_ord wrk
WHERE
  srq.srq_id      = wrk.srq_id
AND srq.start_dts > TRUNC(sysdate) + 7.5/24
AND wrk.WO_STAT   = '104'
GROUP BY
  wrk.WO_ID,
  srq.SRQ_ID,
  srq.ASDL_UNID,
  srq.CSDL_SEQ_NO,
  srq.ASDL_CMD,
  HOST_CLLI
ORDER BY
  proc_time DESC;

Can anyone please help me where is the problem?

SQL itself looks good, i'd say that there will be problem with escaping. Is this a standalone file or is it actually in shell script ? If yes, paste also the surrounding shell code. — tvm, Dec 04 '13 at 14:48
select 'WO_ID, SEQ_NO, ASDL,NE, START_TIME, COMP_DT, PROC_TIME' from sys.dual; SELECT wrk.WO_ID||','||srq.CSDL_SEQ_NO||','||srq.ASDL_CMD||','||srq.HOST_CLLI As NEP||','||min(srq.START_DTS) as start_dt||','||max(srq.COMP_DTS) as comp_dt||','||((max(srq.COMP_DTS)-min(srq.START_DTS)) * 86400) as proc_time FROM SARMPRD1.TBL_ASDL_LOG srq, sarmprd1.tbl_wrk_ord wrk where srq.srq_id = wrk.srq_id and srq.start_dts > trunc(sysdate) + 7.5/24 and wrk.WO_STAT = '104' group by wrk.WO_ID, srq.SRQ_ID, srq.ASDL_UNID, srq.CSDL_SEQ_NO, srq.ASDL_CMD,HOST_CLLI order by proc_time desc; spool off — user2642751, Dec 04 '13 at 14:53

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

Problem lies in the concatenation operator. Since you concatenate all the columns, you cannot use 'AS NEP', 'AS START_DT', etc. Remove those and it will work fine, remember that you are outputting practicaly only one column, so multiple aliases don't work.

For an useful alternative, see this thread.

edited May 23 '17 at 11:49

Community

1
1

answered Dec 04 '13 at 14:57

tvm

3,263
27
37

score 0 · Answer 2 · answered Dec 04 '13 at 15:00

You have syntax errors, with concatenate operator ...

here's your original:

select 'WO_ID, SEQ_NO, ASDL,NE, START_TIME, COMP_DT, PROC_TIME' from sys.dual; 
SELECT wrk.WO_ID||','||srq.CSDL_SEQ_NO||','||srq.ASDL_CMD||','||srq.HOST_CLLI As NEP||','||min(srq.START_DTS) as start_dt||','||max(srq.COMP_DTS) as comp_dt||','||((max(srq.COMP_DTS)-min(srq.START_DTS)) * 86400) as proc_time FROM SARMPRD1.TBL_ASDL_LOG srq, sarmprd1.tbl_wrk_ord wrk where srq.srq_id = wrk.srq_id and srq.start_dts > trunc(sysdate) + 7.5/24 and wrk.WO_STAT = '104' group by wrk.WO_ID, srq.SRQ_ID, srq.ASDL_UNID, srq.CSDL_SEQ_NO, srq.ASDL_CMD,HOST_CLLI order by proc_time desc

Try this:

select 'WO_ID, SEQ_NO, ASDL,NE, START_TIME, COMP_DT, PROC_TIME' from sys.dual; 
SELECT wrk.WO_ID||','||srq.CSDL_SEQ_NO||','||srq.ASDL_CMD||','||srq.HOST_CLLI As NEP||','||min(srq.START_DTS) as start_dt,
    max(srq.COMP_DTS) as comp_dt,
    ((max(srq.COMP_DTS)-min(srq.START_DTS)) * 86400) as proc_time 
FROM SARMPRD1.TBL_ASDL_LOG srq, sarmprd1.tbl_wrk_ord wrk 
where srq.srq_id = wrk.srq_id 
  and srq.start_dts > trunc(sysdate) + 7.5/24 
  and wrk.WO_STAT = '104' 
group by wrk.WO_ID, srq.SRQ_ID, srq.ASDL_UNID, srq.CSDL_SEQ_NO, srq.ASDL_CMD,HOST_CLLI 
order by proc_time desc

score 0 · Answer 3 · answered Dec 04 '13 at 15:01

I don't see the solution to your problem, but here is a suggestion:

Create a new script with just a few items from your original script in it. Run it and see if it works. If so, add a bit more code and verify that works. Continue doing this until it fails. The problem line should then be easily identified.

ORA-00923: FROM keyword not found where expected using shell script

3 Answers3