Re: Help DECODING a result
From: Garry M. Filimonov <garry_at_fly.triniti.troitsk.ru>
Date: 1996/09/18
Message-ID: <01bba557$26dd5d80$bf0000c5_at_Garry.triniti.troitsk.ru>#1/1
Date: 1996/09/18
Message-ID: <01bba557$26dd5d80$bf0000c5_at_Garry.triniti.troitsk.ru>#1/1
For complex conditions in SQL expr you can use next rules:
DECODE( <DECODE_condition>,0, <false_expr>,<true_expr>).
or
DECODE( Sign(<DECODE_condition>), 1, <true_expr>, <false_expr> ). /* if you like dBASE
IIF(,,) function */
Where:
Condition DECODE_condition ---------------------- --------------------------------- A<B Decode( Sign(A-B), -1, 1, 0 ) A<=B Decode( Sign(A-B), 1, 0, 1 ) A>B Decode( Sign(A-B), 1, 1, 0 ) A>=B Decode( Sign(A-B), -1, 0, 1 ) A=B Decode( A, B, 1, 0 ) A between B and C Decode( Sign(A-B), -1, 0, Decode(Sign(A-C), 1, 0, 1 )) A is null Decode(A,null,1,0) A is not null Decode(A,null,0,1) A in (B1,B2,...,Bn) Decode(A,B1,1,B2,1,...,Bn,1,0) nor LogA Decode( LogA, 0, 1, 0 ) (1-Sign(LogA)) LogA and LogB LogA * LogB LogA or LogB LogA + LogB LogA xor LogB Decode(Sign(LogA), Sign(LogB), 0, 1) or Mod( Sign(LogA), Sign(LogB), 2 )
E.g. your decode next:
Decode( Sign(number_value),1,Decode(number_value-0.4),1, 0, 1),0)
return 1 for 0<number_value<=0.4 or 0 else.
-- G'luck ! ----------------------------------------- Garry M. FIlimonov LASU TRINITI, Troitsk, MR, Russia garry_at_triniti.troitsk.ru 7-(095)-334-0408 Patrick McCarthy <pmccarth_at_rpc.unb.ca> wrote in article <51mk7c$c8m_at_sol.sun.csd.unb.ca>...Received on Wed Sep 18 1996 - 00:00:00 CEST
>
> 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
>