Re: Avoid multiple scan of the same table

madhusreeram_at_gmail.com wrote:
> On Aug 21, 9:04?am, mak..._at_gmail.com wrote:
> > Is there way to avoid multiple scan of the same table in following?
> >
> > Select c1 as v1, c2 as v2, c3 as v3
> > From t1
> > Union
> > Select c1 as v1, c2 as v2, c4 as v3
> > From t1
> > Where ?c5 < 5000
> >
> > The output should look like..
> >
> > v1, v2, v3
> > v11, v21, v31
> > v12, v22, v32
> > and so on.
> >
> > Rows from the both queries can be same so UNION is required to avoid
> > duplicates in current construct.

> It looks like you want to select c4 if c5<5000 else c3.
> If that's the case, then:
> select c1 as v1, c2 as v2, decode(sign(c5-5000),-1,c4,c3)
> From T1;

That does not necessarily produce the same results as the original query:

1. For rows where c5 < 5000, there will be a result row with c3 and a result row with c4, which will both be kept if the columns have different values.
2. Duplicate rows within a given half of the original will be removed.

The latter could be fixed with DISTINCT. How to fix the former depends on whether the OP really wants these results (my experience has been that in half these cases or more the original query is not actually correct).

-- 
_______________________________________________________________________
Dan Blum					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."