Re: A SQL Problem

Agi Chen heeft geschreven in bericht ...
>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

Dear Agi,

Try using the sign and decode functions. Below is possible the answer for your problem.

select stk_no

,           year
,          decode(sign(in-out),1,in-out,null) "RESULT(+)"
,          decode(sign(in-out),-1,ABS(in-out),null) "RESULT(-)"

sUCCes,

Jan-Marcel Received on Fri Mar 05 1999 - 11:02:17 CET