Archives of the TeradataForum

Message Posted: Wed, 27 Sep 2006 @ 10:01:40 GMT

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

Regarding UNIONing a table back together with a view....

As Christian said, this would work, but probably isn't a great idea. First, I'd guess that each of those queries in the UNION has to be resolved independently, so you might end up with a row scan being repeated once for each query. Second, you said that some rows might be in more than one part of the UNION. If anybody accesses the view without limiting by your indicator field, that could cause problems.

But there's an easy way around it - use a bit-map style view with an indicator field for each type.

With a table like

     Type     char
     Value    integer

You could do something like

     Select
     case when type = 'A'  or type = 'AB' then 1 else 0 end IsTypeA,
     case when type = 'B' or type = 'AB' then 1 else 0 end IsTypeB,
     case when type = 'C' then 1 else 0 end IsTypeC,
     case when type = 'D' and value < 0 then 1 else 0 end IsTypeDminus,
     case when type = 'D' and value >= 0 then 1 else 0 end IsTypeDplus,
     Value
     >From myTable

This keeps the view at one row per source table row and would access the table once. It also lets you do a little more robust analysis of rows falling/not falling into multiple categories.

David Curley

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