union

building semantic layer
1

Hi all,

I have 16 tables in Oracle, all with the same layout (for ex. fields A,B,C) I want to define an object X in designer as beeing the result of :

select A from table 1
union
select A from table 2
union
…
with no aggregation.

How can I define this Object X in designer. It’s quite simple in SQL, I know.

Thanks Nils
nilsdc@xs4all.be

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

2

Nils & Gerda De Caster - Torck schrieb:

Hi all,

I have 16 tables in Oracle, all with the same layout (for ex. fields A,B,C) I want to define an object X in designer as beeing the result of :

select A from table 1
union
select A from table 2
union
…
with no aggregation.

How can I define this Object X in designer. It’s quite simple in SQL, I know.

this is impossible…

W.

DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

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

3

In a message dated 98-10-15 09:32:51 EDT, you write:

select A from table 1
union
select A from table 2
union
…
with no aggregation.

How can I define this Object X in designer. It’s quite simple in SQL, I know.

this is impossible…

Okay, well, it might be impossible in BusinessObjects. But that just means you need to look for other avenues.

If you create a view on your Oracle database using the same SQL that you listed above, such as:

create view U_VIEW
as
Select A from Table 1
Union
Select A from Table 2
Union
Select A from Table 3

… then you can add that view to your universe, and create an object based on view column A. Whether you add aggregation to that object or not is up to you.

There are other issues with views (performance, etc.) but if you really need this as an object this is a possible solution.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

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

4

One thing to keep in mind when using UNIONS in Oracle…

Oracle has two types of UNIONS, a UNION and a UNION ALL. A UNION will return every unique row from each table; a UNION ALL will return every row from each table, including duplicates.

If you have three tables, T1, T2, and T3, and each has a field A, and each table has a row where A = ‘ABCD’, then a UNION will return 1 row. A UNION ALL will return 3 rows.

Even if field A is distinct across T1, T2 and T3, you still may want to use UNION ALL instead of UNION, even though it makes no difference in the end results. The reason is that UNION has to sort through the results to eliminate duplicates; since UNION ALL allows duplicates, it does not have to sort. Thus, the UNION ALL can be faster than the UNION.

Erich Hurst
Compaq Computer Corporation

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

5

David, you’re right, but the question was how to define a union object in designer. The database view is the only way to do it… Walter

DRathbun@AOL.COM schrieb:

In a message dated 98-10-15 09:32:51 EDT, you write:

select A from table 1
union
select A from table 2
union
…
with no aggregation.

How can I define this Object X in designer. It’s quite simple in SQL, I know.

this is impossible…

Okay, well, it might be impossible in BusinessObjects. But that just means you need to look for other avenues.

If you create a view on your Oracle database using the same SQL that you listed above, such as:

create view U_VIEW
as
Select A from Table 1
Union
Select A from Table 2
Union
Select A from Table 3

… then you can add that view to your universe, and create an object based on view column A. Whether you add aggregation to that object or not is up to you.

There are other issues with views (performance, etc.) but if you really need this as an object this is a possible solution.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’ Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’

–
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

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