Re: A SQL Problem

On Fri, 5 Mar 1999, Agi Chen wrote:

> 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
>
>
>
>
>

Try this one!

select STK_NO, YEAR,

       decode(sign(OUT-IN),1,0,IN-OUT) RESULTPLUS,
       decode(sign(OUT-IN),1,OUT-IN,0) RESULTMINUS

Notice that if you want a space instead of a zero and/or some signs in the header you have to do a bit of manipulating of the data yourself

Email: tomhag_at_rsv.se Received on Fri Mar 05 1999 - 11:36:18 CET