Skip to Main Content
Forums

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Inconsistent datatypes: expected NUMBER got CHAR at OCI call OCIStmtExecute

RaghunandanSep 22 2021

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
This post has been answered by Christian Berg-0racle on Sep 23 2021
Jump to Answer

Comments

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

image.png

Christian Berg-0racle
Answer

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.

Marked as Answer by Raghunandan · Sep 23 2021
Gianni Ceresa

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.

2 people found this helpful
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 .

1 - 5

Post Details

Added on Sep 22 2021
#business-intelligence-applications, #business-intelligence-foundation, #business-intelligence-suite-enterprise-edition-obiee, #obiee, #obiee11g
5 comments
92 views