Re: A SQL Problem

A bit of early morning brain excercise! Try this for size!

create table a
(stk_no varchar2(10),
 year date,
 in_field number,
 out_field number);

Created dummy data....

insert into a values ('0001','1999',300,200);
insert into a values ('0002','1999',340,206);
insert into a values ('0003','1999',100,250);
insert into a values ('0004','1999',400,260);
insert into a values ('0005','1999',300,290);

Select statement....

select
 stk_no,
 year,
 decode( sign(in_field-out_field),1,in_field-out_field,null) "Result(+)",  decode( sign(in_field-out_field),-1,out_field-in_field,null) "Result(-)" from

    a;

Results....

STK_NO YEAR Result(+) Result(-)

0001               1999       100
0002               1999       134
0003               1999                             150
0004               1999       140
0005               1999        10

The only snag is that if in-out = 0. I suppose you can tweak the decode statements to cater for that or have another field Result(=).

Hope it helps.

Mark

Agi Chen wrote in message ...
>Dear all,
>I have a problem in SQL statement.
>
>hmmm....
>
>My table (let's say tableA) desc like this
>
> STK_NO VARCHAR2,
> YEAR DATE ,
> IN NUMBER ,
> OUT NUMBER
>
>some sample data as follows ....
>
>STK_NO YEAR IN OUT
>0001 1999 300 200
>0002 1999 100 150
>0003 1999 500 250
>
>How do I get the report in SQL*PLUS like this ?
>
>
>if IN >= OUT then
> result(+) = IN - OUT
>else
> result(-) = ABS(IN - OUT)
>
>
>STK_NO YEAR RESULT(+) RESULT(-)
>------ ----- --------- ---------
>0001 1999 100
>0002 1999 50
>0003 1999 250
>
>Any idea ???
>
>Best Regards
>Agi Chen
>agi_at_mail.taicom.com.tw
>
>
>
Received on Tue Mar 09 1999 - 10:22:41 CET