 |
 |
Archives of the TeradataForum
Message Posted: Sun, 08 Aug 2004 @ 15:11:22 GMT
| Subj: | | Re: Problem with Union |
| |
| From: | | Dieter Noeth |
Anomy Anom wrote:
| | 16 tables and 16 fields is not a coincidence but a requirement...Basically we are Unioning different instances of the same table 16 times &
each table have 16 fields too... | |
| | The table is a parent child relationship...But the first column have only the highest parent available and the table contains information
on the highest parent only...But we need to get the relationship for each member. | |
A good resource about different ways to represent hierarchies in SQL is Joe Celko's "Trees and Hierarchies".
| | Here 1 is the parent and 2 is its child, 3 the child of 2 and so on... And this table contains details of parents in the level of 1
only...But we need to get the childs for 2 and for 3 and for others...., this is because in the next level we need to query only on the single
field to get all its child.. Thats Y we went for Union the same tables with different field order 16 times, so we get combinations for alll... I
might be wrong here... But logically it seemed to serve our purpose...And then was struck by this Union Issue.... | |
As you Union the same table 16 times, there's another way to get the same result. You need a helper table with consecutive numbers (e.g. from 1
to 16) to cross join:
select distinct
case h.numval
when = 1 then aa
when = 2 then bb
when = 3 then cc
when = 4 then dd
when = 5 then ee
when = 6 then ff
...
end as d1,
case h.numval
when = 1 then bb
when = 2 then cc
when = 3 then dd
when = 4 then ee
when = 5 then ff
when = 6 then gg
...
end as d2,
case h.numval
when = 1 then cc
when = 2 then dd
when = 3 then ee
when = 4 then ff
when = 5 then gg
when = 6 then hh
...
end as d3,
...
from aaa crosss join helper_table h
where h.numval between 1 and 16
If the result set is different from the Union query, then there's a bug with Union...
Dieter
| |
