## Message Posted: Sun, 08 Aug 2004 @ 15:11:22 GMT

 < Last>>

 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".

 eg: 1 2 3 4 5 6..16

 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

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2004 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback