Archives of the TeradataForum
Message Posted: Mon, 12 May 2008 @ 20:45:13 GMT
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:
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,
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|