Archives of the TeradataForum

Message Posted: Tue, 13 May 2008 @ 16:04:13 GMT


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

Subj:		Re: View with Default and Null values

From:		Michael Larkins

Hello Srikanth:

I am always happy to help if I have something to offer that seems appropriate.

I have run your queries under TD 12.0 and the non-joined rows with NULL are coming back as expected. As I mentioned previously, using an outer join to bring back only the missing non-joined rows uses more resources (spool, disk, and processor for holding joined rows and then throwing them away at the end) than necessary. That is why I suggested the NOT EXISTS which keeps only the non-joined rows. I suggested it instead of NOT IN to include any rows that might in real life have a NULL in column1, if that is possible.

Regardless, back to your question:

When I do a left outer join with this view as the inner table joining on Col1, I expect Col2 'Default' value to appear only when there is a match on the join condition.

This is a correct assessment on your part.

The 'Group By' clause works, as expected; however, when I run the select clause, it appears as if all rows have matched on the join condition (even though they are not) hence, 'Default' is selected for ALL rows.

I am not sure, but it sounds like you are interpretting this incorrectly. The SELECT with the count(*) showed correct counts with a count of 1 for 'Default' - when the 1 in column1 of table1 matched the 1 in column1 of table2 (regardless of the view). The ? (NULL) is the one with a count(*) of 2 - the non-matching rows from the outer join. As you mentioned, table2/view_tbl2 is the inner table. Whenever a row from the inner table is not found to match a row of the outer table, all column references from the inner table are "extended with NULL value."

As mentioned by a couple of people, the earlier releases had a bug regarding selecting a literal in the view and its evaluation when part of an outer join. The optimizer seemed to assume that a literal would never produce a NULL in the WHERE clause, even when it is coming from the inner table. This was a wrong assumption. As mentioned above, this bug is fixed in TD 12 and maybe later releases of V2R6. I am not sure when it came out and don't remember if Dieter mentioned which release.

From my way of thinking, this is simply a second and more important reason (other than inefficiency) to not use an outer join to find non- matching rows. Otherwise, you have to wait until a later release to get the correct answer.

Don't know that this adds much more value to what has already been discussed or entirely answers your concern.

Regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor


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

Attachments

Library

Quick Reference