Archives of the TeradataForum

Message Posted: Tue, 16 Dec 2003 @ 14:22:04 GMT

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

Rachana,

You are right in assuming that the removal of the alias name will give you the right result, but there is no 'problem' here.

When you give the RMU_SCHED_MMS_GET_MISSION_RES1 table an alias, the system then expects you to use the alias name when referring to the table columns.

If you do not use the alias, but instead use the full table name, the system treats this as a reference to a second instance of the same table.

So now you have two instances of the RMU_SCHED_MMS_GET_MISSION_RES1 table which are not joined with an ON condition. This creates a Cartesian PRODUCT join and, since the table has two rows, your answer set has 2x2, i.e. 4 rows.

In your case the incorrect answer was easily spotted, the danger with this fairly common coding oversight is that there is no warning message and the answer set looks reasonable.

It highlights the importance of running EXPLAINS, where the PRODUCT JOIN to the second instance of the table would have been described.

Regards,

Bill

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