Archives of the TeradataForum
Message Posted: Fri, 14 Jun 2002 @ 08:39:22 GMT
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
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|