Datatype of an oracle table field changes when pulled into cognos as custom sql

[sakthivelmani]

Hi All,

I am trying to write a report using Custom sql. The original datatype of the Oracle table fields are changed when i pull them into report studio using custom sql. Is there any way , i can bring the fields with their original datatype. I need this because my template alignment settings will only work if the datatype is correct. Please let me know if you guys have come across a situation like this.

Thank you all in advance,
Sakthi

[gpollock]

In your custom SQL query, are you explicitly casting the data items to the desired types, or are you having Cognos "guess"?

[sakthivelmani]

I am not casting the datatypes.

[gpollock]

That's your problem.  When you run custom sql, cognos will look at a sample of the results and guess at what the datatype should be.  Sometimes it guesses correctly, and sometimes it doesn't--it's usually related to the sample of the results.

Try casting the fields that you need to be a certain data type.  Either in your sql query, or change the data type of the individual data items.

[bdbits]

I would note that if you created a proper package, you could solve this in FM one time for all reports you create. You should not normally be creating reports with SQL anyway.

Just saying....