to_char function in 10.2.1 version

[mprof]

Hi,

until now I've been working with version 10.1 we have upgraded it to 10.2.1
I had a report that was using the function to_char (to_char([SQL1].[SDATE],'mm-dd-yyyy'))
but now I'm getting an error regarding the to_char function:

UDA-QOS-0006 Error
UDA-SQL-0219 The function "to_char" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.

Any ideas how to fix it without changing much?

thanks

[BigChris]

Hi,

Where are you using that function in your report? I've seen that sort of error message when I've been using it in report header and/or layout calculations...

C

[mprof]

Im using it in a data Item.
I have a query that i'm choosing dates but in the report it shows it as timestamp with hours etc.
I want it to be just 'mm-dd-yyyy'.
but when i'm trying to apply the to_char function i'm getting an error.

[mprof]

ok i've found the problem...
the in the sql properties U have defined SQL Syntax as pass-through, I have changed it back to Native and it works fine...
but Now I'm having problem with the sql code...
the code contains a WITH clause and it is not being supported.
any Ideas?

[bdbits]

Although there are rare cases, you should almost never need to be coding SQL statements in your report queries. It bypasses a big part of why you should be using Cognos in the first place. I would personally rebuild the query using standard data items pulled from the Cognos metadata, if that is at all possible.

All that said, it sounds like you are using Oracle. So if you copy your original query and paste it into PLSQL Developer or whatever you are using for your Oracle query tool, does it work? Using the pass-through mode is roughly equivalent, so if it works in the query tool it should execute in your report. If you intend to use native syntax, there are limitations you can read about in report studio help.

Also, why is to_char being used twice? I am not at all an Oracle expert, but I believe the inner to_char should already be returning you a varchar2.

[Lynn]

Although there are rare cases, you should almost never need to be coding SQL statements in your report queries. It bypasses a big part of why you should be using Cognos in the first place. I would personally rebuild the query using standard data items pulled from the Cognos metadata, if that is at all possible.

All that said, it sounds like you are using Oracle. So if you copy your original query and paste it into PLSQL Developer or whatever you are using for your Oracle query tool, does it work? Using the pass-through mode is roughly equivalent, so if it works in the query tool it should execute in your report. If you intend to use native syntax, there are limitations you can read about in report studio help.

Also, why is to_char being used twice? I am not at all an Oracle expert, but I believe the inner to_char should already be returning you a varchar2.

AGREE AGREE AGREE!!

I'd also suggest that you may not need to convert the data item at all, just set the data format property in the layout as desired. Further, the use of vendor specific functions, such as to_char, are sometimes necessary, but using equivalent Cognos functions  can make your reports more portable in the future should your company decide to switch vendors. This happens more often then you'd suspect.