Re: Help DECODING a result

Patrick McCarthy wrote:
>
> I am running Oracle 7.1.4 and I am attempting to write a sql statement to
> decode results to appear on a report (Developer 2000) in a certain format.
>
> >>>>>>>>>>>>>>>>>>
> select nais_results.component, DECODE(number_value,NULL, '--ND--',
> 0, '--ND--',
> 8888888,'--NA--',
> number_value)
> from nais_results
> where submission_id = :submission_id and nais_results.component = 'BENZENE'
> AND CONDITION != 'MODIFIED'
> ORDER BY SAMPLE_ID
> ;
> >>>>>>>>>>>>>>>>>>>
>
> What I need is the following;
>
> If number_value = 0 then 'ND' (Not Detected)
> If number_value = NULL then 'ND' (Not Detected)
> If number_value = 8888888 then 'NA' (Not Applicable)
> If number_value (between 0 and 0.4) = 'TR' (Trace Levels)
> ELSE number_value.
>
> My problem is, I do not know how to do the values which fall between 0 and
> 0.4. I cannot use BETWEEN in the DECODE statement as is does a search for a
> particular value. What I need is some other way of doing this ??? Any
> suggestions would be greatly appreciated.
>
> Thanks
>
> Patrick McCarthy
> pmccarth_at_rpc.unb.ca

Patrick,

The decode statement can actually figure out if a number is less than another number if you use the sign function.

decode(sign(10 - 4),1,'First # is greater',-1,'First number is less')

You see by taking the sign (either 1 or -1) of a math function you can actually determine whether a value is less than another.

Hope this helps
Brian Motzer Received on Tue Sep 17 1996 - 00:00:00 CEST