Inconsistent datatypes: expected NUMBER got CHAR at OCI call OCIStmtExecute

Raghunandan

Hi Team,

I am getting below error while creating measure while converting tableau formula to OBIEE RPD(12.2.1.4)

Tableau Formula : DATE(STR(MONTH([Earliest Date]))+"/"+STR([[[[[Billing Cycle]]]] Start 1])+"/"+STR(YEAR([Earliest Date])))

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: 1843, message: ORA-01843: not a valid month at OCI call OCIStmtFetch. (HY000)

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

SQL Issued: SELECT 0 s_0, CAST(CAST(Month("AR Analytics"."Fact WNS AR RR Select Billinginfo"."Earliest Date") AS CHARACTER(30))||'/'||CAST("AR Analytics"."Fact WNS AR RR Select Billinginfo"."Billing Cycle Start 1" AS CHARACTER(30))||'/'||CAST("AR Analytics"."Fact WNS AR RR Select Billinginfo"."Billing Cycle Start 1" AS CHARACTER(30)) AS DATE) s_1 FROM "AR Analytics" FETCH FIRST 10000000 ROWS ONLY.

If i removed CAST AS Date then it is running fine and giving below output

1/15/15

10/1/1

11/15/15

Here i need to convert above output(11/15/15) to Date format.

can anyone help me here

Regards,

sudheer

Gianni Ceresa

ORA-01843: not a valid month

This is a database error, it isn't the BI server complaining, but the database receiving the query doesn't like it.

Look at the generated physical query (you posted the logical query), and find the error there. It will lead you to easily fix your LSQL formula once you understand what the database doesn't like.

Also keep in mind that you are doing lot of SQL operations, maybe it is worth to consider preparing your database a bit better for the analysis you plan to do?

Raghunandan

Below is the physical query generated

Christian Berg-0racle

That would be a 1:1 transformation of Tableau to OA I assume? Because the conversions are dreadful and assured to give you the most headache possible and conceivable for the most punishing performance input and also least maintainability.

Gianni Ceresa

https://community.oracle.com/tech/apps-infra/discussion/comment/16809998#Comment_16809998

Ok, but did you try to debug the query to find your error?

Nobody here has your database to run your query against it and see where the issue is.

Decompose all those conditions in small pieces, focus mainly on what tries to convert something into a date/month or the other way round as the error clearly tells you that "something" isn't a valid month.

Raghunandan

I tried to debug the same but unable to convert the below output to Date format

1/1/2017

10/1/2016

tried to_datetime function but no luck. is there any other way to do this .