Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Union

Status
Not open for further replies.

138006

IS-IT--Management
Dec 30, 2003
101
IN
Hi
I have 2 fact tables. Now I want to write 2 SQLS for the 2 facts joined by UNION.

e.g.

(
SELECT D1.A1, D2.A2, 0, 0, sum(F1.A1), sum(F1.A2)
FROM D1, D2, F1
WHERE ........
GROUP BY D1.A1, D2.A2
UNION
SELECT D1.A1, D2.A2, sum(F2.A1), sum(F1.A2), 0, 0
FROM D1, D2, F2
WHERE ........
GROUP BY D1.A1, D2.A2
)


As u can see, objective is to get 2 values from 2 different fact tables in the same row.

Any solutions??
Thanks
 
That looks like BO SQL to me - simple but not a lot you can do to the temp tables. That's why MSI goes without it. Also, Union is very "irresponsible", becasue you could have the following result:

A1 A2 SumF1 SumF2 SumF3 SumF4
----------------------------------------
you me 10 20 0 0
you me 0 0 30 40

While desired report should be:
A1 A2 SumF1 SumF2 SumF3 SumF4
----------------------------------------
you me 10 20 30 40


In MSI, you can get both results. With Union, you can only get one.

Back to you question, you don't have to worry about it - just define attribute A1 and A2, and metrics from both tables, MSI SQL will take care of the joins. You may need to specify the outer join direction (left or right for different fact table, or full). Either way, you would not see the 'UNION', but you will get the data as you desired, hopefully.






 
Why not use the VLDB property called "UNION Multiple INSERT" but use it only for this report (as opposed to using it project wide)?

Have you tried that, or anything similar?

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top