2 Replies Latest reply on Jul 1, 2020 12:15 AM by 1865011

    OBIEE - Presentation Variable - Apostrophe

    1865011

      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?