Archives of the TeradataForum

Message Posted: Tue, 25 Nov 2003 @ 10:34:41 GMT

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

For Christian Schiefer, this is not a "sloppy" code. Many CRM solutions are based on views. This code (SELECT *) is used in views frequently. Derived tables are suppossed to be dynamic replacements of views; in Teradata documentation for v.2.5 with many query processing enhancements. This undocumented error cannot be reported as a feature, it is a real bug. It slows my report optimization of 4 TB warehouse with 300 views, subviews and ad hoc selects a lot :)

Especially for Vivek Pandey, I made a stupid error posting non-ANSI "Teradata" version naming the derived table together with columns.

"SELECT * FROM (SELECT A,B,C FROM ...) AS X (id1,id2,id3)". Be sure the syntax is correct.

This is a modified ANSI version.

/* NOT WORKING ANSI */
SELECT *
FROM
(       SELECT
          A.*
         ,C.id AS id2
         ,D.id AS id3
        FROM       ( SELECT 1 AS id1 ) AS A
        CROSS JOIN ( SELECT 1 AS id  ) AS C
        CROSS JOIN ( SELECT 1 AS id  ) AS D
) AS X;

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