Re: SQL -- minimizing table access

"Bosco Ng" <boscong_at_leccotech.com> wrote in message news:<3ec0fd39$1_at_shknews01>...
> I have a original query conceptually like this:
>
> select 1,
> sum(C1)
> from tableA
> where C2 < 0
> union
> select 2,
> sum(C1)
> from tableA
> where C2 > 0
>
> Since this is a union query, it makes table access to tableA for 2 times,
> which in fact a single access
> of tableA should be enough, so I rewrite the query to something like this:
>
> 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
>
> ? how can I turn the display back to its original form with minimal effort??
>
> Thx a lot

Use concatenation with carriage return to get the look -

select

	max(case when C2 > 0 then 1 else 1 end)||'   '||
	sum (case when C2 > 0 then C1 else 0 end)||chr(10)||
	max(case when C2 < 0 then 2 else 2 end)||'   '||
	sum (case when C2 < 0 then C1 else 0 end) "#   SUM"
	from tableA;

This produces:

# SUM 1 100
2 200

... but the whole thing is a SINGLE row, SINGLE column, if that makes a difference.

chr(10) works on Unix. I think Windoze requires chr(13)||chr(10) for the same effect; you might want to check it.

• GG