Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OBIEE - Presentation Variable - Apostrophe

Received Response
1
Views
2
Comments
User_MS7GY
User_MS7GY Rank 1 - Community Starter
edited Aug 13, 2024 5:17PM in Oracle Analytics Forum

in OBIEE, we have an analysis that use a direct database query.

there is a filter in the sql: AND (ap_suppliers.vendor_name = '@{pvVendor}{}'  OR '@{pvVendor}{}' is null )

in the report we use a dashboard prompt to filter the results:

type: column value (supplier.supplier name)

set a variable: presentation variable (pvVendor)

the report works correctly for suppliers without an apostrophe in the name, e.g. Staples

the report errors when a supplier has an apostrophe in the name, e.g. O'Leary

View Display Error  

Error generating view.

Error getting cursor in GenerateHead  

Error Details Error Codes: OAMP2OPY:OPR4ONWY:U9IM8TAC:OI2DL65P 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: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. [nQSError: 17001] Oracle Error code: 907, message: ORA-00907: missing right parenthesisat OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed.(HY000)

is there a way to write the sql or prompt to overcome this issue?

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Direct DB requests should only ever be a "last refuge" solution for precisely the issue that it doesn't support all the things the normal platform tool is capable of.

    So I advise that you take 3 steps back and think about why you are doing things the way you are doing them in the first place and to think about why you do not use the tool as it is designed.

  • User_MS7GY
    User_MS7GY Rank 1 - Community Starter

    it will work in BI if you update the sql filter to: AND ( ap_suppliers.vendor_name = q'[@{pvVendor}{}]' OR q'[@{pvVendor}{}]' is null)