Archives of the TeradataForum
Message Posted: Mon, 12 May 2008 @ 17:51:46 GMT
I am noticing an issue in Teradata, when I do a left join with a view and do a select based on a field in the view being null. This field has a default value defined.
Here is the set up -
TABLE1 has 1 column, COLUMN1, and 3 rows.
TABLE2 has 2 columns (COLUMN1, COLUMN2) and 1 row.
A view on TABLE2 defines a default value for COLUMN2.
When TABLE1 is joined with TABLE2 on COLUMN1 where COLUMN2 is null, it should return 2 rows, but returns NONE. The group by shows that COLUMN2 has 2 nulls.
CREATE SET TABLE TABLE1 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( column1 INTEGER ) PRIMARY INDEX ( column1 ); insert into TABLE1 values (1); insert into TABLE1 values (2); insert into TABLE1 values (3); CREATE SET TABLE TABLE2 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( column1 INTEGER, column2 VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC ) PRIMARY INDEX ( column1 ); insert into TABLE2 values (1, 'One'); CREATE VIEW VIEW_tbl2 AS SELECT column1, 'Default' Column2 FROM TABLE2; SEL * FROM VENTR_DEV.VIEW_tbl2; SELECT TB2.column2, count (*) FROM TABLE1 TB1 left outer join VIEW_tbl2 TB2 on TB1.column1 = TB2.column1 Group By 1 <<--- Shows 2 rows where Column2 is null Select * from TABLE1 TB1 left outer join VIEW_tbl2 TB2 on TB1.Column1 = TB2.Column1 where TB2.Column2 is NULL; << --- Returns none when selected.
My problem is why the last SQL does not return any rows. My hypothesis is that since the view has a default defined, it assumes this field will have default value for all rows in the join, even though this is a left join and NOT ALL rows may have a match. Regardless, I think this is not ideal. Can the members confirm if this is not an acceptable behavior or a bug? Any workarounds (short of not using defaults in the view?)
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|