5.0 Conversion Problem

building semantic layer
1

Hi Listers

Since I was already at it, I thought I’d ask about another problem with 5.0.

We have about 40 Production reports that we created off of 1 Universe in Version 4.1.4. In each of the reports, we have an object that is the result of concatenating two other objects. If there are Null results in one of the other objects, we want the concatenation to result in the word “NONE”.

Here is the syntax of that object:

= If IsNull() Then " NONE" Else (+" " +)

These reports were in our Production environment for 6 months and had no problem. In 5.0.1, half work and half do not. They are all based off of the same universe. When I look at the SQL for the ones that do not work, it appears to work fine. To test it, I run it interactively against our Sybase database, and the results are fine. When I View Data, the results for the Destination Arrival Code are 1/0/1900 00:00:00 AM and the Destination Arrival Text is 1/0/1900 00:00:00 AM. If I View Data on the ones that do work, Destination Arrival Code and Destination Arrival text are correct. All of the 40 reports work fine in BO 4.1.4.

Does anyone have any suggestions? BO support isn’t helping as they tell me that the universes must be different, but there is only 1 universe.

Any help or ideas are greatly appreciated.

Snezana Ogrizovic
Quad/Graphics
Decision Support Specialist

Listserv Archives (BOB member since 2002-06-25)

2

Snezana

If you have an oracle database here’s the sql for an OBJECT (not based on other objects) that you need;

decode(tablename.DestinationArrivalCode,NULL,‘None’,tablename.DestinationArrivalCode||’ '||tablename.DestinationArrivalText)

This will return ‘None’ for null values on tablename.DestinationArrivalCode or the two fields concatenated when the field is not null.

Mike McErlain
McErlain Consulting @ Cisco Systems
MikeMcErlain@Netscape.net

At 11:00 AM 1/13/2000 -0500, you wrote:

Hi Listers

We have about 40 Production reports that we created off of 1 Universe in Version 4.1.4. In each of the reports, we have an object that is the result of concatenating two other objects. If there are Null results in one of the other objects, we want the concatenation to result in the word “NONE”.

Here is the syntax of that object:

= If IsNull() Then " NONE" Else (+" " +)

Listserv Archives (BOB member since 2002-06-25)

3

Snezana Ogrizovic,
Re Concatenated Null values
I don’t know if you’re using an Oracle database, but if you are have you thought about trying the NVL function, works everytime for me (admittedly in a 4.1.5 environment)

Listserv Archives (BOB member since 2002-06-25)