One Object, 2 table sources

building semantic layer
1

[moved to Designer forum - Andreas]

Newbie here.

I have a object in two different tables in a universe. I need to create an object in the same universe that will get its value from the one that is not empty.

Can this be done? The new object would need to check the values of the objects in the 2 tables and then pick the one that has a value.

Thanks for any help on this one.

bsastry :canada: (BOB member since 2004-04-29)

2

Would it possible for you to post the query which you are trying to acheive through objects.

It looks feasible as I have used two tables in single object defination. But would be better if you can put exact requirements.

JaiGupta (BOB member since 2002-09-12)

3

I have a object in two different tables in a universe.

OK. In example, table MINNIE contains column FLOWER, and table DAISY_DUCK contains a column called FLOWER.

I need to create an
object in the same universe that will get its value from the one that is
not empty.

Warning to cartesian products!! How are joined table MINNIE and table DAISY_DUCK? Are they joined? With what criteria?
If table MINNIE has 2,000,000 rows and table DAISY_DUCK has 2,000 rows, how have I to select the column FLOWER in order to avoid the cartesian product?

Can this be done? The new object would need to check the values of the
objects in the 2 tables and then pick the one that has a value.

This can be done if MINNIE table and DAISY_DUCK table have the same number of rows: is that always granted by the intrinsic structure of your DataBase?

I suggest to create a VIEW of the two tables, but they have to be linked with a join.

Make us an example stating your DB situation, please! :wink:

Christian Konrads :it: (BOB member since 2004-07-21)

4

bsastry, it may also help if we knew what database you are using (DB2, Oracle, SQL Server) and the data type of FLOWER (character, numeric).

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

5

I’m not sure what you mean by “empty”.

Using Sybase, I’ve done this:

isnull(DSY.OWNER.DAISY.FLOWER,DSY.OWNER.MINNIE.FLOWER)

I joined both DAISY and MINNIE to the main table with outer joins. If the corresponding record isn’t in DAISY, I get the one from MINNIE.

KSG :us: (BOB member since 2002-07-17)

6

Thanks for the feedback - here is some of the requested info:

Running BO5.1.6 on Oracle8i 8.1.7.4.0

Following the example from Christian,

MINNIE and DAISY_DUCK are joined through the main table, GOOFY, using the same dimensions.
The field is a date, and is always populated either in MINNIE or DAISY_DUCK, but never in both.

bsastry :canada: (BOB member since 2004-04-29)