OBIEE - Presentation Variable - Apostrophe

User_MS7GY

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?

[Deleted User]

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

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