Archives of the TeradataForum

Message Posted: Wed, 03 May 2000 @ 13:05:53 GMT

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

I'm getting a strange result when using an SQL statement with nested derived tables.

System: 4700 running V2R2.1

Basically, the SQL does the following (full SQL is available, if necessary)

     SELECT some columns
     FROM table1, table2, table3, table4, table5 (all inner joins)
     WHERE some joins & conditions
     GROUP BY  some columns
     HAVING quantity <> 0>
     ORDER BY some columns

Table5 is a derived table:

     SELECT columns
     FROM tableA
     LEFT JOIN tableB
     ON some conditions

TableA is a derived table:

     SELECT columns
     FROM datatable
     WHERE some conditions

TableB is a derived table:

     SELECT columns
     FROM datatable
     WHERE some conditions

The main SQL returns a value that is double the correct value. On investigation, the result of the derived table, table5, is two rows when it should be 1 row.

Run individually, the SQL from tableA, tableB and table5 return 1 row each. When put back into the main SQL, table5 returns 2 rows.

If table5 is made a (real) temporary table, and the results of tableA and tableB are inserted into the temp table, which is then used in the main SQL in place of the derived table, the results are correct.

Is there a limit on the depth of nesting derived tables? Is there an error in the logic of this SQL? Has anyone else had similar problems?

We have a work-around in which the derived table, table5, is created using 1 SQL statement instead of 2 derived tables, and this produces the correct results.

Thanks for any ideas!

Barbara George
Manager, Decision Support, Pick 'n Pay Information Systems
Cape Town, South Africa

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