Archives of the TeradataForum

Message Posted: Mon, 12 May 2008 @ 20:45:13 GMT


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

Subj:		Re: View with Default and Null values

From:		Michael Larkins

Srikanth:

I am not exactly sure what you are trying to accomplish with view_tbl2, but I am pretty sure you are doing it incorrectly. You are not setting a default, you are guaranteeing that the word 'default' will be selected every single time. Therefore, there will be no nulls, ever. Are you trying to use 'default' only when there is no value in column2? If so, try this: select column1, coalesce(column2,'default') from table2;

As for the other part of your problem, here is the output from the EXPLAIN for your select:

`Explanation --------------------------------------------------`
	`1)`	`First, we do a single-AMP JOIN step from SQL00.TABLE2 by way of the primary index with unsatisfiable conditions derived by SAT, which is joined to SQL00.TB1 by way of the primary index with unsatisfiable conditions derived by SAT. SQL00.TABLE2 and SQL00.TB1 are joined using a merge join. The result goes into Spool 1 (one-amp), which is built locally on that AMP. The size of Spool 1 is estimated with low confidence to be 1 row. The estimated time for this step is 0.03 seconds.`
	`->`	`The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.03 seconds.`

The optimizer is smart enough to know that you are asking for nulls in a column that will always have the value 'default' and it will never be null. Do you see where the EXPLAIN says, "unsatisfiable conditions?" It is talking about your WHERE clause.

Now, it would appear that you are looking for all none matching rows. This is not the way to do it. Why do an outer join and then turn around and throw away the matching rows? Makes no sense. Instead, try something like this:

     Select *
     from TABLE1 TB1
     where NOT exists (sel * from view_tbl2 where tb1.column1=Column1)

This is sufficient because you are looking for rows without a match in view_tbl2. Therefore, a join is not needed to build a row. The only columns available will be those in table1.

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor


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

Attachments

Library

Quick Reference