Re: Oracle Discover - Decode

"LMoscarito" <lmoscarito_at_aol.com> wrote in message news:20001010221004.20940.00001332_at_ng-fg1.aol.com...
> I wanted to use decode to create aging columns for invoices.
> I can do it whenit equals values, however I wanted to end it with a range
> over 330 days. But decode will not accept >.
> Does any one know a work around.
> ex decode(trunc((sysdate-receipt_date)/30)>5, amount,null) over330
> I read you can use sign but do not know how.
> Thanks,
> Joe

sign returns a -1, 0, or 1 if the number is negative, zero, or positive:

sign(-4) = -1
sign(0) = 0
sign(12) = 1

Fake decode into doing what you want by doing this:

sign(5-(trunc((sysdate-receipt_date)/30))))

And decode the result of that:

decode(sign(5-(trunc((sysdate-receipt_date)/30))),-1,amount,null)

There's also a way with "case", IIRC, but it's with Oracle 8 only and I don't recall the syntax offhand. Someone posted it about a week ago. Search for a topic dealing with "case" in SQL*Plus.

-Matt Received on Wed Oct 11 2000 - 05:40:25 CEST