KB10184: “Error: QueryEngine encountered error: Call SQLExecDirect got SQL_ERROR [22018:1722: on HSTMT] [DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-01722: invalid number” error message appears in MicroStrategy Enterprise Manager reports.

SYMPTOM:
Users are unable to execute certain reports in the Strategy Enterprise Manager project when the Enterprise Manager Warehouse resides on Oracle9i. Upon further investigation, it appears that only reports referencing the view IS-SESSION_FACT and a column IS_CONNEC_M_ID or IS_DISCON_M_ID are affected.
The following error is logged in DSSErrors.log when the report is executed:
Error: QueryEngine encountered error: Call SQLExecDirect got SQL_ERROR ORA-01722: invalid number
CAUSE:
This issue occurs due to the date format used in the Enterprise Manager Warehouse and the 'SUBSTR' function used in the definition of the view IS_SESSION_FACT to compute the values of IS_CONNEC_M_ID and IS_DISCON_M_ID.
For example, if the date format 'YYYY:MM:DD:HH24' is used, the report returns the "Invalid number" error.
However, when the date format 'YYYY:MM:DD' or 'YYYYMMDDHH24' are used, the report executes without error.
ACTION:
There are two solutions to resolve this issue:

• Change the date format in the Enterprise Manager Warehouse, as outlined in the following Strategy Knowledge Base document:
  
• KB1100-000-0007 - How to set the Strategy ODBC Driver for Oracle Wire Protocol to accept Date in the same default format as Oracle ODBC drivers?
• Alternately, run the script to modify the view definition against the Enterprise Manager Warehouse, as described in the following Knowledge Base document:
  
• KB6400-751-0261 - IS_SESSION_FACT definition for Oracle leads to incorrect results for session/user related analysis in Strategy Enterprise Manager 7i - 7.2.x and 7.5.x.