The code I had in question was pointing to an OIA 11g DB repository.

My code:

with usrOutstanding (usrOut,usrManOut,usrCreatedate,usrUpdateDate) as
(select count(user_id), IDU.username, IDC.createdate,IDC.updatedate
from rbacxservice.ID_cert_users IDCU
left join rbacxservice.ID_certs IDC
on iDC.id = IDCU.cert_id
left join rbacxservice.IDC_users IDU
on IDC.certifier_id = IDU.iam_user_id
where IDCU.works_for_me = 1
and IDC.state !=4
and IDCU.user_id in
(select distinct (user_id) from rbacxservice.IDC_user_acct_attrs IDCUSACCATT
where user_id in ( select distinct(user_id) from rbacxservice.IDC_user_acct_attrs IDCUSACCATT
where certified is null))
group by IDU.username,IDC.createdate,IDC.updatedate),
usrTotal(usrTot,usrMan) as (
select count(user_id),IDU.username from rbacxservice.ID_cert_users IDCU
left join rbacxservice.ID_certs IDC
on iDC.id = IDCU.cert_id
left join rbacxservice.IDC_users IDU
on IDC.certifier_id = IDU.iam_user_id
where IDC.createdate between to_date('01-Apr-13') and to_date('10-Jul-13')
group by IDU.username),
usrManagers (manUsername, manVP, manFullName, manTitle, manDept)
as (select distinct (IDU.username),
IDU.customproperty8, IDU.firstname || ', ' || IDU.lastname as fullname, IDU.title, IDU.customproperty2
from rbacxservice.ID_cert_users IDCU
left join rbacxservice.ID_certs IDC
on iDC.id = IDCU.cert_id
left join rbacxservice.IDC_users IDU
on IDC.certifier_id = IDU.iam_user_id
group by IDU.username, IDU.customproperty8, IDU.firstname || ', ' || IDU.lastname, IDU.title, IDU.customproperty2)
select manvp, manfullname, mantitle, mandept,
round(((usrtot - usrout)/usrtot)*100)||'%' as percentageComplete,
usrtot - usrout||'/'||usrtot,
usrCreatedate,
usrUpdateDate
from usrOutstanding USRO
left join usrTotal USRT
on USRO.usrManOut = USRT.usrMan
left join usrManagers USRM
on USRM.manUsername = USRT.usrMan

I created several reports, accumulating 500+ lines of sql code, various CTE's, and all combined with various joins. When I got to my last SQL code, I ended up getting an ORA-00905 missing keyword error. I thought that was weird because the SQL code could be executed within SQL developer though not within BI publisher? Google suggested I should remove the REM'd out lines, which I did without a result.

After cutting and slicing, I discovered that the CTE name was the issue. Turned out it was because I used the word with the letters i and n together. This was an issue because even though it was merged as one string, it was using the letters together to make the word in, which is a syntax word used within SQL to be used for multiple returns of data.

This took me a while to discover because this should not be an issue, though be aware if you use CTE's within BI publisher's data model.

About the author