Re: Help DECODING a result

pmccarth_at_rpc.unb.ca (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

You might try a second decode in the decode statement: select nais_results.component, DECODE(number_value,NULL, '--ND--',

                                                    0,  '--ND--',
                               8888888,'--NA--',
		decode(sign(0.4 - number_value),-1, '--TR--',
                                 number_value))