Re: sql select
In article <6so9al$d23$1@news.xmission.com>, Syed <sas@mastic.gov.my> wrote:
>
>Dear friends,
>
>How do I make two columns in my table to be two rows in my report? for
>example, I have a table called cash flow and its structure is like this
>
>DATE CASH INFLOW CASH OUTFLOW
>---- ----------- ------------
>1/1/98 4,000 2,000
>2/1/98 3,000 1,000
>1/2/98 5,000 1,000
>1/3/98 4,000 3,000
>
>I want to produce a report like this
>
>Type 1/98 2/98 3/98
>---- ----- ----- -----
>a)CASH INFLOW 7,000 5,000 4,000
>b)CASH OUTFLOW 3,000 1,000 3,000
>-------------------------------------
>NET FLOW(a-b) 4,000 4,000 1,000
>-------------------------------------
>
>I am not good at sql, however I am sure sql can do this.
I would do this in ACE or 4GL myself. However, if you really
need to do it in pure SQL then something like this comes close:
First: setenv DBDATE DMY4/
(since your dates are formatted the British way - dd/mm/yy)
Then this SQL:
create temp table t1 ( date1 date,
cash_inflow integer,
cash_outflow integer ) with no log ;
insert into t1 values ( "1/1/98", 4000, 2000 ) ;
insert into t1 values ( "2/1/98", 3000, 1000 ) ;
insert into t1 values ( "1/2/98", 5000, 1000 ) ;
insert into t1 values ( "1/3/98", 4000, 3000 ) ;
select "a) CASH INFLOW" Type, ( select sum(cash_inflow)
from t1
where month(date1) = 1 ) jan_98,
( select sum(cash_inflow)
from t1
where month(date1) = 2 ) feb_98,
( select sum(cash_inflow)
from t1
where month(date1) = 3 ) mar_98
from systables where tabid = 1
union
select "b) CASH OUTFLOW" Type, ( select sum(cash_outflow)
from t1
where month(date1) = 1 ),
( select sum(cash_outflow)
from t1
where month(date1) = 2 ),
( select sum(cash_outflow)
from t1
where month(date1) = 3 )
from systables where tabid = 1
union
select "net flow (a-b)" Type, ( select sum(cash_inflow) - sum(cash_outflow)
from t1
where month(date1) = 1 ),
( select sum(cash_inflow) - sum(cash_outflow)
from t1
where month(date1) = 2 ),
( select sum(cash_inflow) - sum(cash_outflow)
from t1
where month(date1) = 3 )
from systables where tabid = 1
order by 1
returns this:-
type jan_98 feb_98 mar_98
a) CASH INFLOW 7000 5000 4000
b) CASH OUTFLOW 3000 1000 3000
net flow (a-b) 4000 4000 1000
Obviously it is a drag having to add select clauses for each month, and
I'm not sure for which versions of the software you can get away with
this select-within-a-select construct.
- Paul (not a spokesman)