Re: Help DECODING a result

Patrick,

You may try this for a value fall in a range of 0 to 0.4

Decode(smallest(0, number_value), 0, decode(largest(0.4, number_value), 0.4, 'TR'), ..... )

David Ng

> 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
Received on Thu Sep 26 1996 - 00:00:00 CEST