Re: SQL -- minimizing table access

From: Lothar Armbruester <lothar.armbruester_at_t-online.de>
Date: Tue, 13 May 2003 18:58:36 +0200
Message-ID: <PM0003BD8E9B320F64@hades.none.local>

Bosco Ng wrote:
[...]

> select
> sum(case when temp.C2 > 0 then
> C2
> else
> 0
> end) column_1,
> sum(case when temp.C2 < 0 then
> C2
> else
> 0
> end) column_2
> from
> (select C2
> from tableA) temp
>
>
> after the rewrite, I found it to be faster, requiring only half of
> logical
> read which is my expectation.
> But the problem is that the original display like this:
>
> 1 100
> 2 200
>
> becomes like this:
>
> 100 200
>

Hello Bosco,
try this:

select a,sum(c1)
from

   (select

       case when c2<0 then 1 else 2 end a,
       c1

    from tablea
    where c2<>0)
group by a;

or even this:

select

   case when c2<0 then 1 else 2 end,
   c1
from tablea
where c2<>0
group by

   case when c2<0 then 1 else 2 end

since I did not test these SQLs, you might have to adjust the syntax slightly.

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de

Received on Tue May 13 2003 - 11:58:36 CDT