Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>   <<First <Prev

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


  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023