I have two repository variables: aquarter and ayear. How do I set variables instead of hardcoded values?
SELECT * FROM TABLE(ORACLEBI.PKG_CASH_LIMITS.fn_b2export(2,2012))
and it gives no error, but also this query doesn't return any data.
SELECT * FROM TABLE(ORACLEBI.PKG_CASH_LIMITS.fn_b2export('valueof(aquarter)','valueof(ayear)'))
Help me please, I'm stuck.
Odbc driver returned an error (SQLExecDirectW). Error Details Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 1722, message: ORA-01722: invalid number at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000) SQL Issued: SELECT 0 s_0, "VOLKSBANK"."cash_limits"."ACCNAME" s_1 FROM "VOLKSBANK" FETCH FIRST 2000001 ROWS ONLY
select * from table(ORACLEBI.PKG_CASH_LIMITS.FN_B2EXPORT('d','t')) * Error at line 1 ORA-01722: invalid number
and then filtered query result using OBIEE Analysis prompts on columns.
create or replace view oraclebi.vcash_limits as with quarts as ( select 1 as q from dual union all select 2 as q from dual union all select 3 as q from dual union all select 4 as q from dual ), years as ( select extract(year from sysdate) r from dual union all select extract(year from sysdate) - 1 r from dual union all select extract(year from sysdate) - 2 r from dual ), prompts as ( select quarts.q, years.r from quarts, years ), tsql as ( SELECT t.q, t.r, d.* FROM prompts t, TABLE(ORACLEBI.PKG_CASH_LIMITS.FN_B2EXPORT(t.q,t.r)) d/*(+)*/ ) select * from tsql t