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


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>...

>
> 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 Wed Sep 18 1996 - 00:00:00 CEST

Original text of this message