Re: Avoid multiple scan of the same table
From: <madhusreeram_at_gmail.com>
Date: Thu, 21 Aug 2008 08:20:20 -0700 (PDT)
Message-ID: <924499a6-c7ad-489d-b30c-91aee687b7d5@y38g2000hsy.googlegroups.com>
Date: Thu, 21 Aug 2008 08:20:20 -0700 (PDT)
Message-ID: <924499a6-c7ad-489d-b30c-91aee687b7d5@y38g2000hsy.googlegroups.com>
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;
-Madhu Sreeram Received on Thu Aug 21 2008 - 10:20:20 CDT