IF( boolean condition, True Value, False Value )
The IF condition evaluates the “boolean Condition” and if the “boolean Condition” is true, then it returns the “True Value”. Otherwise, it returns the False Value.
Example:
if( 10>20, 10,20) // 20
CASE Statement
//Simple Case
CASE Selector
WHEN Expr THEN res1
WHEN Expr THEN res2
.....
.....
WHEN ExprTHEN resn
ELSE result
END
In Simple case we have to specify a Selector, Which determines which group of action to get executed.
The selector is a Value that determines , Which WHEN clause should be executed.
The selector is evaluated only once for the whole selection process.
Simple Case internally evaluates the selector using the equality operator ,Directly upon the process and Compares with the Exprs.
All the results must be of same datatype. This is the value returned once a condition is found to be true.
Simple Case is not suitable for multi Conditional Analysis and other than the Equality Operator.
Example:
CASE deptno
WHEN 10 THEN 'TEN'
WHEN 20 THEN 'TWENTY'
ELSE 'NOT FOUND'
END
//Searched Case
CASE
WHEN SeaarchCondition1 THEN res1
WHEN SeaarchCondition2 THEN res2
.....
.....
WHEN SeaarchConditionN THEN resN
ELSE result
END
In searched case ,the Selector is not present,It has Series of search Condition, That are evaluated in Order to Determine Which Group of Actions to take place.
A searched case stmt has search conditions that yield Boolean values TRUE,FALSE,NULL
When A particular search Condition Evaluates to True, Then group of Stnts Associated with this conditions are Excuted.
Example:
CASE
WHEN deptnp=10 THEN 'Ten'
WHENdeptno=20 THEN 'Twenty'
ELSE 'NOt Found'
END
COALESCE( val1,val2,.....)
The COALESCE function returns the fist not NULL value from the list of values. If all the values in the list are NULL, then it returns NULL.
Example: COALESCE(NULL,10,20,NULL) //10
Comments
Post a Comment