BusinessObjects Board

Merging Issue Due to Database Datatype?

building reports webintelligence
#1

I am having trouble merging two objects in a WebI report. We are on 4.2 SP09 Patch 4. Both objects have a type of Number in WebI. One of my queries is based on Oracle database and the other is a free-hand SQL query based SQL Server 2014.

Through lots of trial and error I believe my issue is with my free-hand SQL query.

So I have created a simplified example…

image
image917×700 39.9 KB

(I do not know why there are quotes around BRNCH_NBR. I removed them in the universe and they just re-appear. That is not my primary issue though.)

Branch Number is of type Numeric and Affinity Number is of type String in the universe. They are Numeric(7, 0) and Char(3) in the database, respectively.

I clicked the View Script button and copied the generated SQL and dropped into a new free-hand SQL query.

When I try and merge Branch Number from my universe query and BRNCH_NBR from my free-hand SQL query I get the following error.

image

A coworker suggested that I force BRNCH_NBR from my free-hand SQL query to be a number with a variable with this…

=ToNumber([BRNCH_NBR])

When I merge Branch Number from my universe query to that variable it works. It also works when I change the qualification of BRNCH_NBR in my free-hand SQL query from Dimension to Detail as shown below…

image
image725×563 27.8 KB

I do not understand why I need to do either of those.

Noel

#2

My guess would be that, even though WebI interprets both data types as number, the underlying data type of each database is different enough that the merge engine can’t merged them. That you have to explicitly declare one or both of them to get the merge to work doesn’t surprise me.

1 Like
#3

Correct. You cannot merge between the two. You can call a string a number but it’s still a string at heart. It also prevents you getting errors, given that the string has the capability to hold non-numbers.

1 Like
#4

Did you try creating a variable to convert the object to a number and use the variable in the merge?

James

#5

I did that and it worked. I just don’t think I should have to.

#6

check if it is really a numeric 7.0 in the source DBs and not e.g. a decimal
and check if there is any content that can be interpreted differently ( . , - )
so DB-driver will give you different results/formats from these two source-DBs.