1

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?

8

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 .....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.