Archives of the TeradataForum

Message Posted: Tue, 13 May 2008 @ 14:32:43 GMT

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

Mike -

Thank you for your reply, I always admire you for your responses even when the posts lack detail (either intentional or oversight). Regarding the problem I posted, Dieter and a couple more have confirmed this to be a bug. Your reply addresses how I can get the results I wanted, but my concern is with the (incorrect and inconsistent) results I am getting with current query. This is a simplified case, so it may be rewritten, but in reality, there are several other larger queries that rely on the view to do a left outer join, and rewriting them all can be time consuming.

Now back to my query, for a moment ignore that there is a base table for my view, and the view simply returns a Col1 and 'Default' for Col2. 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. The 'Group By' clause works, as expected; how ever, 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 think the optimizer got a bit ahead of itself in this case, assuming that Default will prevail after the join. If I materialized the view into a table the results are consistent with the group by (and what I expect them to be). Please run the last two statements (group by and select) again. Even more awkward is that you can run the Group by query without specifying the 'Group By' clause with a view, may be fallout of the same bug. The following query runs, notice that there is no Group By clause. I cannot run the same with a table TABLE2 instead of VIEW_tbl2.

     SELECT TB2.column2, count (*)
     FROM TABLE1 TB1 left outer join VIEW_tbl2 TB2
     on TB1.column1 = TB2.column1

Results -

     Column2  Count(*)
     Default     1
     ?           2

Dieter - I am told we are running 2.6. What version is the bug fixed, and is there any case number to seek the bug fix from Teradata?

Thanks.

Srikanth

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