Multiples IFs statement
This pattern defines a function that uses conditional statements over local variables.
Components:
Function Declaration:
CREATE FUNCTION functionName(parameters) RETURN returnType
Declares the function with input parameters and the return type.
Variable Declaration:
Declares variables, including the return variable.
BEGIN-END Block with IF Statement:
Check if a condition is true.
Each case is used to assign a value over the same variable.
Conversion:
DECLARE SECTION
: variables with default an expression are moved to a common table expression.
IF/ELSE
statement can be handled using the CASE EXPRESSION
inside the select allowing conditionals inside the queries.
RETURN
statement is transformed to the final select.
CREATE OR REPLACE FUNCTION Case1 (
in_date_ IN DATE,
min_max_ IN VARCHAR2 )
RETURN DATE
IS
cond_date_ DATE := CURRENT_DATE;
BEGIN
IF ( in_date_ IS NULL ) THEN
IF ( min_max_ = 'MIN' ) THEN
cond_date_ := FOO1();
ELSE
cond_date_ := FOO2();
END IF;
ELSE
cond_date_ := TRUNC(in_date_);
END IF;
RETURN cond_date_;
END Case1;
CREATE OR REPLACE FUNCTION Case2 (
year_ IN NUMBER,
id IN NUMBER)
RETURN VARCHAR2
IS
base_value_ NUMBER;
fully_depritiated_ VARCHAR2(5);
residual_value_ NUMBER;
acc_depr_prev_ NUMBER;
acc_depr_ NUMBER;
BEGIN
base_value_ := FOO1(year_, id);
acc_depr_ := FOO2(year_, id);
acc_depr_prev_ := FOO3(year_, id);
residual_value_ := NVL(base_value_,0) -(acc_depr_ + acc_depr_prev_);
IF (residual_value_=0 AND base_value_!=0) THEN
fully_depritiated_ := 'TRUE';
ELSE
fully_depritiated_ := 'FALSE';
END IF;
RETURN fully_depritiated_;
END Case2;
CREATE OR REPLACE FUNCTION Case2_1 (
year_ IN NUMBER,
id IN NUMBER)
RETURN VARCHAR2
IS
base_value_ NUMBER;
fully_depritiated_ VARCHAR2(5);
residual_value_ NUMBER;
acc_depr_prev_ NUMBER;
acc_depr_ NUMBER;
BEGIN
base_value_ := FOO1(year_, id);
acc_depr_ := FOO2(year_, id);
acc_depr_prev_ := FOO3(year_, id);
residual_value_ := NVL(base_value_,0) -(acc_depr_ + acc_depr_prev_);
IF (residual_value_=0 AND base_value_!=0) THEN
fully_depritiated_ := 'TRUE';
ELSE
fully_depritiated_ := 'FALSE';
END IF;
fully_depritiated := fully_depritiated || ' CONCAT FOR TESTING';
fully_depritiated := fully_depritiated || ' CONCAT FOR TESTING2';
RETURN fully_depritiated_;
END Case2;
CREATE OR REPLACE FUNCTION Case2_1 (
year_ IN NUMBER,
id IN NUMBER)
RETURN VARCHAR2
IS
base_value_ NUMBER;
fully_depritiated_ VARCHAR2(5);
residual_value_ NUMBER;
acc_depr_prev_ NUMBER;
acc_depr_ NUMBER;
BEGIN
base_value_ := FOO1(year_, id);
acc_depr_ := FOO2(year_, id);
acc_depr_prev_ := FOO3(year_, id);
residual_value_ := NVL(base_value_,0) -(acc_depr_ + acc_depr_prev_);
IF (residual_value_=0 AND base_value_!=0) THEN
fully_depritiated_ := 'TRUE';
ELSE
fully_depritiated_ := 'FALSE';
END IF;
fully_depritiated := fully_depritiated || ' CONCAT FOR TESTING';
fully_depritiated := fully_depritiated || ' CONCAT FOR TESTING2';
RETURN fully_depritiated_;
END Case2;
Known Issues
No issues were found.
Related EWIS
SSC-FDM-OR0042: Date Type Transformed To Timestamp Has A Different Behavior.
SSC-EWI-0073: Pending Functional Equivalence Review.
SSC-EWI-OR0036: Types resolution issues, the arithmetic operation may not behave correctly between string and date.
Last updated