Re: Avoid multiple scan of the same table

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
>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.

Like this:

select
  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.

Regards,
Jaap. Received on Sun Aug 24 2008 - 13:23:35 CDT