null sorting issue

[nancysinghal]

Hi All,

I have an issue in report, where I have to sort the report on the basis of two columns, A and B, first by A and then by B. The column A can have multiple values of B. Column B datatype is varchar, and it has few blank(empty space) values as well. I want column B values to be sorted in ascending order with blank values at the last.

By default, cognos is taking null values at the top. Cognos version I am using is 10.2.1.

Please suggest.

Regards,
Nancy

[Invisi]

Nancy, usually you avoid NULL in a data warehouse. What possibilities do you have to have this issue solved at the data warehouse level?

[nancysinghal]

Thanks Invisi :)

Actually it got sorted by using nvl(trim(column_name),9999999) for sorting. That blank value is as per functional requirement and we cannot avoid it.

Regards,
Nancy

[Invisi]

nvl should work indeed. I am currently using coalesce to fill NULL values in reports. I am curious though why a NULL value is a functional requirement. What kind of attribute is this field about? And why do they want NULL values instead of some value representing 'missing', 'unknown' or something similar? If you care to share this (offtopic).

[BigChris]

As Invisi is alluding to, you can certainly keep the NULL in your operational environment (which seems to be a requirement), but replace it with something more meaningful in your BI environment.

[Invisi]

As Invisi is alluding to, you can certainly keep the NULL in your operational environment (which seems to be a requirement), but replace it with something more meaningful in your BI environment.

Good clarification. With giving it some value, I mean in the data warehouse, not in the source application.