I am unable to use a Direct Database Request (DDR) as a filter query. The requirement is to filter postal codes in my main subject area-based query using a DDR-based query which performs a REGEXP function, explained below.
Is there any way to define or set the value of a presentation or request variable inside my filter query?
What other approaches could I try? The REGEXP function is not function-shippable to the RPD via EVALUATE() in an OBIEE analysis, due to being not supported in my NQSConfig.INI file. And, this is an Oracle Sales Cloud instance, so I do not have access to modify my NQSConfig.INI file.
What I've tried so far:
I've created a DDR in OBIEE using the Connection Pool defined in my RPD.
This query retrieves all postal codes that are non-numeric:SELECT
hz_parties.party_id,
hz_parties.postal_code
FROM
HZ_PARTIES
where
REGEXP_INSTR(Substr(HZ_PARTIES.POSTAL_CODE,0,2), '[0-9]{2}') = 0
This works.
But I have an OBIEE subject-area based analysis which needs a filter using operator is based on results of another analysis, using the DDR and matches against its party_id column.
This is the generated error and SQL of my main OBIEE analysis:
Error Details
Error Codes: YQCO4T56:OPR4ONWY:U9IM8TAC:OI2DL65P
Location: saw.views.evc.activate, saw.httpserver.processrequest, saw.rpc.server.responder, saw.rpc.server, saw.rpc.server.handleConnection, saw.rpc.server.dispatch, saw.threadpool.socketrpcserver, saw.threads
Odbc driver returned an error (SQLExecDirectW).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27047] Nonexistent table: "EXECUTE". (HY000)
SQL Issued: {call NQSGetLevelDrillability('SELECT "Contact"."Contact Row ID" saw_0 FROM "Sales - CRM Customers and Contacts Real Time" WHERE "Contact"."Contact Row ID" IN (SELECT saw_0 FROM (EXECUTE PHYSICAL CONNECTION POOL "CRM_OLTP"."Connection Pool" SELECT hz_parties.party_id, hz_parties.postal_code FROM HZ_PARTIES where REGEXP_INSTR(Substr(HZ_PARTIES.POSTAL_CODE,0,2), ''[0-9]{2}'') = 0 ) nqw_1 )')}
