Bi Publisher 11g (OBIEE) ORA-00905: missing keyword

Like most developers would typically do, I created a more comprehensive Oracle SQL code outside the realms of the bi Publisher with the intention on importing this directly into BI publisher thus creating a pre-defined data model for my custom report.
August 6, 2013
Bi-Publisher-11g

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

https://technicalconfessions.com/images/postimages/postimages/_199_2_missing word ORA 100905.png

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.


SOLUTION

https://technicalconfessions.com/images/postimages/postimages/_199_4_issue with data model in bi publisher 11g.png

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

Daniel is a Technical Manager with over 10 years of consulting expertise in the Identity and Access Management space.
Daniel has built from scratch this blog as well as technicalconfessions.com
Follow Daniel on twitter @nervouswiggles

Comments

Other Posts

HOW TO provision AD group membership from OpenIDM

OpenIDMICFAD-connector

June 15, 2018
Created by: Daniel Redfern
For what I see, there's not too many supportive documentations out there that will demonstrate how provision AD group membership with the ICF connector using OpenIDM. The use of the special ldapGroups attribute is not explained anywhere in the Integrators guides to to the date of this blog. This quick blog identifies the tasks required to provision AD group membership from OpenIDM to AD using the LDAP ICF connector. However this doesn't really explain what ldapGroups actually does and there's no real worked example of how to go from an Assignment to ldapGroups to an assigned group in AD. I wrote up a wiki article for my own reference: AD group memberships automatically to users This is just my view, others may disagree, but I think the implementation experience could be improved with some more documentation and a more detailed example here.
Read More...

ForgeRock OpenIDM - InvalidCredentialException: Remote framework key is invalid

ICFIDMOpenIDMOpenICF

November 8, 2017
Created by: Daniel Redfern
In the past, the similar error occurred though for the Oracle Identity Management solution. invalidcredentialexception remote framework key is invalid Because they all share the ICF connector framework, the error/solution would be the same.
Read More...

org.forgerock.script.exception.ScriptCompilationException: missing ; before statement

IDMsync.confforgerockopenidm

November 8, 2017
Created by: Daniel Redfern
org.forgerock.script.exception.ScriptCompilationException: missing ; before statement
Read More...

ForgeRock IDM - org.forgerock.script.exception.ScriptCompilationException: missing ; before statemen

OpenIDMsync.confForgeRock

September 17, 2017
Created by: Daniel Redfern
ForgeRock IDM - org.forgerock.script.exception.ScriptCompilationException: missing ; before statement
Read More...

Caused by: org.forgerock.json.resource.BadRequestException: Target does not support attribute groups

OpenIDMForgeRockICFConnector

September 17, 2017
Created by: Daniel Redfern
When performing the attempt of a reconciliation from ForgeRock IDM to Active Directory, I would get the following error
Read More...

ForgeRock OpenIDM - InvalidCredentialException: Remote framework key is invalid

OpenIDMForgeRockICFConnectorAD

September 17, 2017
Created by: Daniel Redfern
In the past, the similar error occurred though for the Oracle Identity Management solution. invalidcredentialexception remote framework key is invalid Because they all share the ICF connector framework, the error/solution would be the same.
Read More...

ERROR Caused by com.google.api.client.auth.oauth2.TokenResponseException 400 Bad Request - invalid_g

OpenIDMIDMGoogleGoogle-AppsICFreconciliation

September 12, 2017
Created by: Daniel Redfern
During the reconcilation from OpenIDM to the ICF google apps connector, the following error response would occur. ERROR Caused by com.google.api.client.auth.oauth2.TokenResponseException 400 Bad Request - invalid_grant
Read More...

forgerock-openidm-encryptedjwt-error

OpenIDMIDMForgeRockJWTIAM

August 29, 2017
Created by: Daniel Redfern
Received the JWT error
Read More...

Unexpected character ('¾' (code 190)): expected a valid value

ForgeRock-OpenIDMOpenIDMIDMKeystore

June 25, 2017
Created by: Daniel Redfern
Unexpected character occurred when the IP addresses changes and the virtual instance was migrated into a separate network subnet.
Read More...

OpenDJ Error - Connect Error Result Code: 91 (Connect Error)

OpenDJLDAPldapsearchForgeRock

June 5, 2017
Created by: Daniel Redfern
When trying to connect, I would then receive the following error "Connect Error Result Code: 91 (Connect Error)"
Read More...