Archives of the TeradataForum

Message Posted: Fri, 14 Jun 2002 @ 08:39:22 GMT

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

Hi,

I have a problem with OUTER JOIN in combination with the function COALESCE. The problem consists in an incorrect interpretation of the left outer join between a table and a view when the view contains the COALESCE function for null values treatment.

An example of SQL statements (run in Queryman) with the result returned follows:

create table T_1
( Id_1 integer not null, Desc_1 VARCHAR(6))
unique primary index (Id_1);
insert into T_1 (1,'Text_1');
insert into T_1 (2,'Text_2');
insert into T_1 (3,'Text_3');
insert into T_1 (4,'Text_4');

create table T_2
( Id_2 integer not null, Desc_2 VARCHAR(6))
unique primary index (Id_2);
insert into T_2 (2,'Text_2');
insert into T_2 (4,'Text_4');

replace view V_2 as select
coalesce(Id_2,Id_2,0) as V_Id_2,
coalesce(Desc_2,Desc_2,'Error') as V_Desc_2
from T_2;

select T_1.Id_1, T_1.Desc_1, V_2.V_Id_2, V_2.V_Desc_2
from T_1 left join V_2 on T_1.Id_1 = V_2.V_Id_2
order by 1;

Returned Result:
Id_1 Desc_1 V_Id_2 V_Desc_2
   1 Text_1      0 Error
   2 Text_2      2 Text_2
   3 Text_3      0 Error
   4 Text_4      4 Text_4

According to the OUTER JOIN definition in the TD manual SQL Reference - Volume 6, Data Manipulation Statements, 2-9 (and the following sections) I expected the following result:

Id_1 Desc_1  Id_2  Desc_2
   1 Text_1  null  null
   2 Text_2     2  Text_2
   3 Text_3  null  null
   4 Text_4     4  Text_4

There is no such problem when trying the same on Oracle 8, but the MS Access 2000 behaviour is incorrect too.

Thank you very much for your advice

Kind regards

Jiri Basler

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