Re: Avoid multiple scan of the same table
From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 24 Aug 2008 13:18:30 -0700
Message-ID: <1219609098.888523@bubbleator.drizzle.com>
>
> 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.
Date: Sun, 24 Aug 2008 13:18:30 -0700
Message-ID: <1219609098.888523@bubbleator.drizzle.com>
Jaap W. van Dijk wrote:
> 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.
At moments like this don't you have fond memories of the old mainframe days when business requirements were put into writing and when people asked for help they knew enough to state the business rules.
Oh the good old days. <g>
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Sun Aug 24 2008 - 15:18:30 CDT