"ORA-01843: not a valid month" even though there is no date column

[Sonnfan]

Hello community,

after upgrading from Cognos 8 to 10 I am having strange problems with reports that worked fine on Cognos 8.

For example I have a view with not a single date or number format (see Attachement, snippet from SQL Developer). There are two colums for a date, but both are varchar. The error also occurs when I remove the two date colums!

When I create a package containing the view, upload it, go in the browser into Report Studio, create a list and then just drag and drop the whole view into it, after starting the report I get the following error:

UDA-SQL-0114 Der für Operation "sqlOpenResult" angegebene Cursor ist nicht aktiv.UDA-SQL-0107 Allgemeiner Ausnahmefehler während der Operation "open result".ORA-01843: not a valid month RSV-SRV-0042 Zurückverfolgen:RSReportService.cpp(722): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(263): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_RequestRSASyncExecutionThread.cpp(808): [etc]

(Translation of the German parts: declared cursor for the operation "sqlOpenResult" not active, general exeption error whilst the operation "open result")

That happens to different reports, be it old imported ones or new created ones. I see no pattern, when it occurs, and seeing that this mistake also apperars in views without any colums, which could cause this error, makes the whole thing strange.

Has anyone an idea, how I could fix that?

Regards,
Sonnfan

[Lynn]

Wild guess.....do you have a function in your model that is attempting to convert the varchar data to a date datatype? Could it be you have data that is not a valid date format or is somehow failing conversion?

If your SQL is getting generated "as view" rather than minimized you could still get the error even if your report doesn't include the two offending non-date date columns.

I would suggest you look at the generated SQL to see if you run it in a SQL tool so you can isolate the cause. The subject line of your post mentions an ORA error which tells me there is a problem with conversion to a date that is happening in your generated SQL.

[Sonnfan]

Well, the view is a simple "select ... from ..." statement.

I tested it both with and without the OPEN_DATE and CLOSE_DATE in the view itself, both failed in Report Studio.

I don't quite get the second sentence. Do you suggest I test, whether the problem also occurs when the data of the view are in an own table?

ORA errors come from Oracle, that's clear. But all views or tables, that fail to be displayed in report studio, work perfectly fine in SQL Developer. I could maybe test the view/table with one column at a time to try to find the "guilty" one.

I thought maybe there are any date options of Cognos 10 I could change to solve this problem, but it looks like there is no other way than trial and error...

Thanks for your tips though, I will continue testing and see what happens. :)

[Lynn]

The view may be a simple select statement, but are there any functions in your FM model? The SQL generated by the Cognos report is what you need to test in SQL developer. I can't see how Oracle is complaining about a date unless you have a TO_DATE function or a cast function in your FM model. Generate the SQL from the report or look at the query subject in FM.

What I mean by minimized vs. as view depends on how the FM model was built. Minimized SQL means that only the columns included in your report appear in the generated SQL. Say a table has columns A, B, and C and a report only uses A and B. The generated SQL would be something like "select A, B from table". When generated as view the SQL might be something like "with xyz as (select A, B, C from table) select A, B from xyz"

Maybe others might see something I'm missing and provide additional ideas.

[Sonnfan]

Hmm, the only "function" in the view is a to_char which converts tha date to a varchar (which I hoped would kill the error...).

I always used all columns in the report and uploaded a new package when I changed the view, so that it can't be the problem that column C is corrupt ad´nd produces the error while in Report Studio I only use A and B.

I have never looked at this "query subject in FM" (I guess that's a functionality of Report Studio?), I will check that and report here if I find something.

Thanks for all your help!

Edit: I'm pretty sure the problem is Cognos-wise, since it worked on 8.4.

[Lynn]

I have never looked at this "query subject in FM" (I guess that's a functionality of Report Studio?), I will check that and report here if I find something.

I meant look at the Framework Manager (FM) model to see if there are any functions applied.

[Sonnfan]

Ok, so I looked into FM. There aren't any functions applied since I just checked the box in front of the view at the begining where you choose your data sources.

I also assigned all columns (under properties) text- or number-formats per hand, it's still not helping, still the "wrong date format" error in Report Studio...

I also changed all columns to "Attribute" and after that all to "Fact".

Now I have NO IDEA what I could possibly change withing FM, so I'm going to try column per column to see if I find that one faulty column.

Edit: Ok, NOW it get's ridiculous. I deleted all columns exept for the "ID"-column, which looks like "CHG123456", made a new project, package, uploaded it and guess what, still the "wrong date format" error. That new Cognos is killing me. -__-"

Edit2: Ok, so now I just deleted the line "AND OPEN_DATE > '01.01.2012' " of the view (which worked perfectly fine) and now the whole report is working, even with all columns. Now I could restrict the date in Report Studio i guess, but that can't be the solution for all problems, since I get this error as well when I use tables where I just can't delete any statements.

[Sonnfan]

Ok, so right now im handling it this way:

View1:

select "ID",
"CATEGORY","SYM",
(to_char("OPEN_DATE",'DD')||'.'||to_char("OPEN_DATE",'MM')||'.'||to_char("OPEN_DATE",'YYYY')) as OPEN_DATE,
(to_char("CLOSE_DATE",'DD')||'.'||to_char("CLOSE_DATE",'MM')||'.'||to_char("CLOSE_DATE",'YYYY')) as CLOSE_DATE,
to_char("DAYS_OPEN") as DAYS_OPEN,"REQ_KST","ASSIGN_KST",to_char("RN") as RN,"STATUS",to_char("SLA_VIOLATION") as SLA_VIOLATION,to_char("PRIORITY") as PRIORITY from V_TICKETS
order by ID

---------------

View2:

SELECT   "ID",
   "CATEGORY",
   "SYM",
   "OPEN_DATE",
   "CLOSE_DATE",
   "DAYS_OPEN",
   "REQ_KST",
   "ASSIGN_KST",
   "RN",
   "STATUS",
   "SLA_VIOLATION",
   "PRIORITY"
FROM   View1
WHERE to_date("OPEN_DATE",'DD.MM.YYYY') > to_date('01.01.2012','DD.MM.YYYY')
order by ID

--------------

Uploading that last one to Report Studio works.

If now someone could explain to me, why it is like it is I would be very glad. :)

Regards,
Sonnfan

[Lynn]

Could there be some difference in your Oracle client on the Cognos 10 server vs. the client you had for Cognos 8? Or are there different Oracle instances you are pointing to for C8 vs C10?

Could there be a difference with the default date format in Oracle? Here is a link that explains format models for Oracle TO_ functions.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34510

Your first view doesn't make any sense to me. The TO_CHAR function creates a varchar from either a date, a number, or another type of character string and the format models define what the contents of the value you want to convert look like. What is the datatype of OPEN_DATE and what does the data within it look like? Based on the working view you posted, I would guess it is a varchar and it is formatted as DD.MM.YYYY. If so, then the portion of code that says to_char("OPEN_DATE",'DD') implies you want to pull the day of the month from a date field....if the field is a varchar then it wouldn't be correct.

I really don't see how this can be a Cognos error but not seeing all the details makes it hard to confirm.

Have you ruled out a data issue? If you have a string in the data that indicates month number 15 or some other impossible date you could get the error you are seeing. In a SQL tool you would get the error only if you fetch the row with the bad date, so just because it runs in SQL doesn't mean there is no data issue. In Cognos you may be fetching that row if you pull all data.

Ask your DBA to trace what is happening when you submit from Cognos. He/She should be able to see what SQL is being generated and that may help you troubleshoot properly.

[Sonnfan]

Yeah, the first view is crap (sry), it's just a leftover from the time I tried to remove the "wrong date format" error, when I thought the problem was the OPEN_DATE field.

The OPEN_DATE and CLOSE_DATE columns are in DATE-format originally and get changed to VARCHAR in the first view.

The Oracle client is the same in both cases. I still have a machine with Cognos 8 and one with Cognos 10. The same view from the same oracle client works in Cognos 8, but produces this error on Cognos 10.

The data are all correct, too.

------------

I think, i fixed it now. The problem is, that I thought, the OPEN_DATE and CLOSE_DATE with their date-formatted columns were causing the error, so I always left them converted to VARCHAR. Now, that I figured that Cognos (and not only Report Studio, but DM as well) had a problem with the where-statement, I changed that and now it's working with the following code:

select "ID",
"CATEGORY","SYM",

OPEN_DATE,
CLOSE_DATE,

to_char("DAYS_OPEN") as DAYS_OPEN,"REQ_KST","ASSIGN_KST",to_char("RN") as RN,"STATUS", to_char("PRIORITY") as PRIORITY from V_TICKETS
where SLA_VIOLATION = 1

AND OPEN_DATE > to_date('01.01.2012','DD.MM.YYYY')

order by ID

-------------

Regards,
Sonnfan