Archives of the TeradataForum

Message Posted: Wed, 19 Nov 2003 @ 15:10:58 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

I have a query with a UNION of 7 tables (Illustrated below).

All the seperate SELECTS run fine but a union of all 7 selects gives a 2616 error.

I hope that a 2616 error is expected while computing values at a column level & grouping them into a union should not affect the query processing. If so then why do I get a 2616 error (numeric overflow) while unioning the subqueries ?

SELECT *
  FROM ( SELECT 'tab_name_1' (VARCHAR (100)) AS Tab
                , col_g as COMPANY
                , SUM (col_1)    AS sum_1
                , SUM (col_2)    AS sum_2
                , SUM (col_3)    AS sum_3
                , SUM (col_4)    AS sum_4
                , SUM (col_5)    AS sum_5
          FROM tab_name_1
          WHERE col_6 <> 0
          GROUP BY 2

         UNION

SELECT 'tab_name_2' (VARCHAR (100)) AS Tab
                , col_g as COMPANY
                , SUM (col_1)    AS sum_1
                , SUM (col_2)    AS sum_2
                , SUM (col_3)    AS sum_3
                , SUM (col_4)    AS sum_4
                , SUM (col_5)    AS sum_5
          FROM tab_name_2
          WHERE col_6 <> 0
          GROUP BY 2

         UNION

         SELECT 'tab_name_3' (VARCHAR (100)) AS Tab
                , col_g as COMPANY
                , SUM (col_1)    AS sum_1
                , SUM (col_2)    AS sum_2
                , SUM (col_3)    AS sum_3
                , SUM (col_4)    AS sum_4
                , SUM (col_5)    AS sum_5
          FROM tab_name_3
          WHERE col_6 <> 0
          GROUP BY 2

         UNION

         SELECT 'tab_name_4' (VARCHAR (100)) AS Tab
                , col_g as COMPANY
                , SUM (col_1)    AS sum_1
                , SUM (col_2)    AS sum_2
                , SUM (col_3)    AS sum_3
                , SUM (col_4)    AS sum_4
                , SUM (col_5)    AS sum_5
          FROM tab_name_4
          WHERE col_6 <> 0
          GROUP BY 2

         UNION

         SELECT 'tab_name_5' (VARCHAR (100)) AS Tab
                , col_g as COMPANY
                , SUM (col_1)    AS sum_1
                , SUM (col_2)    AS sum_2
                , SUM (col_3)    AS sum_3
                , SUM (col_4)    AS sum_4
                , SUM (col_5)    AS sum_5
          FROM tab_name_5
          WHERE col_6 <> 0
          GROUP BY 2

        UNION

         SELECT 'tab_name_6' (VARCHAR (100)) AS Tab
                , col_g as COMPANY
                , SUM (col_1)    AS sum_1
                , SUM (col_2)    AS sum_2
                , SUM (col_3)    AS sum_3
                , SUM (col_4)    AS sum_4
                , SUM (col_5)    AS sum_5
          FROM tab_name_6
          WHERE col_6 <> 0
          GROUP BY 2

         UNION

         SELECT 'tab_name_7' (VARCHAR (100)) AS Tab
                , col_g as COMPANY
                , SUM (col_1)    AS sum_1
                , SUM (col_2)    AS sum_2
                , SUM (col_3)    AS sum_3
                , SUM (col_4)    AS sum_4
                , SUM (col_5)    AS sum_5
          FROM tab_name_7
          WHERE col_6 <> 0
          GROUP BY 2

  ) viv

ORDER BY 2,1

Thanks, Vivek.

	<Prev	Next>		<<First	<Prev	Next>	Last>>