Re: Avoid multiple scan of the same table
Date: Sun, 24 Aug 2008 18:23:35 GMT
On Thu, 21 Aug 2008 07:04:04 -0700 (PDT), maks71_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
>Select c1 as v1, c2 as v2, c4 as v3
>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.
c1 as v1,
c2 as v2,
case when id = 1 then c3 else c4 end as v3 from t1,
(select 1 id from dual union all select 2 id from dual) dupl where dupl.id = 1 or
(dupl.id = 2 and t1.c5 < 5000 and t1.c3 <> t1.c4)
assuming c3 and c4 are never null, otherwise you have to fiddle with nvl's.
Jaap. Received on Sun Aug 24 2008 - 13:23:35 CDT