Archives of the TeradataForum

Message Posted: Wed, 03 May 2000 @ 14:14:30 GMT

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

I suspect your SQL is the culprit. Without seeing the actual code I can not say exactly what is happening, but from your description prima facie the problem seems to involve the join between derived tables A and B. My primary reason for asserting this is that when you put the results of the join into a temp table, the query worked fine. (Note: when you execute an "insert select" recall that duplicate rows are eliminated.) So if the join between A and B produced two duplicate rows (per key value), they would have been discarded by the insert select operation; thus, it makes sense that the use of the temp table provided the correct results (for all of the erroneous (duplicate) rows would have been eliminated). These rows were not eliminated, though, when you did not use the temp table (for spool files can contain duplicate rows).

Also, you indicated that derived tables A, B, and 5 all returned one row when run as individual queries, but you also reported discovering that derived table 5 was returning 2 rows when executed within the context of the entire query. I don't understand how you could know this fact unless you isolated the results of table 5 and saw that it was returning double the expected rows.

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