Re: Help DECODING a result
Date: 1996/09/26
Message-ID: <324ADA4D.28E6_at_teldta.com>
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
[KSH]: cat /tmp/bart.sql; sqlplus -s / _at_/tmp/bart.sql
create table bart_decode(field1 number(9,2)); insert into bart_decode(field1) values(NULL); insert into bart_decode(field1) values(-1); insert into bart_decode(field1) values(0); insert into bart_decode(field1) values(.2); insert into bart_decode(field1) values(.4); insert into bart_decode(field1) values(.5); insert into bart_decode(field1) values(1); insert into bart_decode(field1) values(7777777); insert into bart_decode(field1) values(7777777.00); insert into bart_decode(field1) values(8888888); insert into bart_decode(field1) values(8888888.00); insert into bart_decode(field1) values(9999999); insert into bart_decode(field1) values(9999999.00);
commit;
PROMPT If number_value = 0 then 'ND' (Not Detected)
PROMPT If number_value = NULL then 'ND' (Not Detected)
PROMPT If number_value = 8888888 then 'NA' (Not Applicable)
PROMPT If number_value (between 0 and 0.4) = 'TR' (Trace Levels)
PROMPT ELSE number_value.
col AA format 9,999,999.90 heading 'Origional|Value'
col BB format a15 heading 'Decode|Value'
select field1 AA,
decode(field1, NULL, lpad('ND',15),
0, lpad('ND',15),
8888888, lpad('NA',15),
decode(sign(field1), -1, lpad(to_char(field1,'9,999,999.90'),15),
decode(sign(field1 - .41), -1, lpad('TR',15),
lpad(to_char(field1,'9,999,999.90'),15))))
from bart_decode;
drop table bart_decode;
exit;
REM END OF FILE
Table created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created.
Commit complete.
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.
Origional
Value DECODE(FIELD1,N
------------- ---------------
ND
-1.00 -1.00
.00 ND
.20 TR
.40 TR
.50 .50
1.00 1.00
7,777,777.00 7,777,777.00
7,777,777.00 7,777,777.00
8,888,888.00 NA
8,888,888.00 NA
9,999,999.00 9,999,999.00
9,999,999.00 9,999,999.00
13 rows selected.
Table dropped.
[KSH]:
--
\\|//
(0-0)
+-----oOO----(_)-----------+
| Brian P. Mac Lean |
| Database Analyst |
| brian.maclean_at_teldta.com |
| http://www.teldta.com |
+-------------------oOO----+
|__|__|
|| ||
ooO Ooo
Received on Thu Sep 26 1996 - 00:00:00 CEST