Archives of the TeradataForum

Message Posted: Tue, 16 Dec 2003 @ 13:32:19 GMT

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

Please look at the query below.

The Select field list does not use the alias name given to the table, instead using the table name directly.

SELECT RMU_SCHED_MMS_GET_MISSION_RES.UNIQUE_ID,
RMU_SCHED_MMS_GET_MISSION_RES.MISSION_START_DATE,
RMU_SCHED_MMS_GET_MISSION_RES.MISSION_END_DATE,
RMU_SCHED_MMS_GET_MISSION_RES.TOTAL_MISSION_SEC,
RMU_SCHED_MMS_GET_MISSION_RES.MISSION_ERR_CODE,
RMU_SCHED_MMS_GET_MISSION_RES.MISSION_ERR_DETAIL_CODE,
RMU_SCHED_MMS_GET_MISSION_RES.CMS_LOAD_DATE
FROM
        RMU_SCHED_MMS_GET_MISSION_RES1 GMR
LEFT OUTER JOIN
        TMP_LOAD_LOG TL
ON
        TL.SCHEDULE_ID = GMR.SCHEDULE_ID
AND
        TL.SESSION_ID = GMR.SESSION_ID

-RMU_SCHED_MMS_GET_MISSION_RES1 table has 2 records in it.

1) After executing above query I am getting 4 rows in the output. Which is not correct. In fact the output has only 2 distinct rows.( each row coming twice in the output)

2) If I change the select fieldList , and say

SELECT *
FROM
        RMU_SCHED_MMS_GET_MISSION_RES1 GMR
LEFT OUTER JOIN
        TMP_LOAD_LOG TL
ON
        TL.SCHEDULE_ID = GMR.SCHEDULE_ID
AND
        TL.SESSION_ID = GMR.SESSION_ID

- Now the query returns me only 2 distinct rows.

It seems that the problem is because of the alias name. Because when I tried the above query again after removing the alias name from everywhere, I got correct result.

Can anyone explain me the reason for this behaviour.

Thanks and Regards,

Rachana

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