In the below code (that I've inherited), I'm getting the message "SQL80001 – incorrect syntax near ')'", the error being on the third line from the bottom with the + sign. I've looked at THIS SO answer to similar issues, but that wasn't the issue here.
select
b.batch_id,
b.submitted_dt,
b.batch_nm,
b.paused_cd,
b.job_cnt,
b.start_dt,
b.finish_dt,
m.module_nm,
m.module_id,
mc.hostname
from
tbl_bat_chess b,
tbl_job_bobs j,
tbl_man_holes m,
tbl_monkey_cokes mc
where
b.finish_dt is null
and to_char(b.submitted_dt, 'DD-MON-YYYY') = to_char(sysdate, 'DD-MON-YYYY')
and (to_number(sysdate - b.submitted_dt) * 1440) > 5 -- 5 minutes
and b.batch_id = j.batch_id
and b.batch_nm like '%EXAMPLE%'
and j.module_id = m.module_id
and m.proc_group_id not in (10, 18)
and j.machine_id = mc.machine_id (+)
group by b.batch_id, b.submitted_dt, b.batch_nm, b.paused_cd, b.job_cnt, b.start_dt, b.finish_dt, m.module_nm, m.module_id, mc.hostname
order by submitted_dt desc
DBMS: PLSQL Developer 10.05.
Oracle Version: 11g
I've manually removed all non-breaking spaces and tried putting square brackets around the line mc.machine_id with no success. How should this be resolved?
Best Answer
SQL80001
is a SQL Server error message. SQL Server doesn't support the old Oracle(+)
syntax for outer joins.Re-write your SQL using
LEFT/RIGHT OUTER JOIN .... ON ....
.