Analysis works as Agent's content but throws error when run by itself.

Question

Hi SMEs,

We have a formula in an analysis (to convert full ....hh:mm:ss date to MM/DD/YYYY) as

CAST(LEFT ("XYZ Date",10) AS DATE)

This analysis runs fine when used in an agent but when run by itself gives an error, as below:

Error Details

Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)

State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)

State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000)

State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 1861, message: ORA-01861: literal does not match format string at OCI call OCIStmtFetch. (HY000)

State: HY000. Code: 17012. [nQSError: 17012] Bulk fetch failed. (HY000)

SQL Issued: SELECT 0 s_0, "Pro.. .  .   ...

Any input is highly appreciated.

[Deleted User] · Answer

https://community.oracle.com/tech/apps-infra/discussion/comment/16823410#Comment_16823410

It's all about data quality and data formatting.

But I repeat my first statement: Have you looked at the detailed log file to see what exactly happens? What does the SQL fetch? Which fetched row contains what? Where is the source of the error?

Again, without that it's all just guesswork.

Gianni Ceresa · Answer

The error is a database error, therefore something is wrong with your data.

Isn't your agent retrieving only a subset of data that, luckily, does respect the format you expect, while the analysis as you run it is missing some filters and retrieve data not matching the format?

As it's a database error it is fairly simple: look at your logs, take the physical SQL query generated by OAS, run it against your database and correct the error you have (either by fixing your data in the database or by adding enough filters in the analysis to not hit the problematic data).

Analytics_User · Answer

we are using OAS 5.9, it is a varchar150 datatype containing YYYY/MM/DD HH:MM:SS at the source. It is being converted to date as  CAST (LEFT ("XYZ Date",10) AS DATE) in the analysis, in the session log it comes as :

cast(substr(XYZ Date , 1, 10) as DATE )   and errors out for ORA-01861: literal does not match format string.