SnowConvert AI - Oracle - CREATE FUNCTION¶
Oracle Create Function to Snowflake Snow Scripting
Description¶
Note
Some parts in the output code are omitted for clarity reasons.
A stored function (also called a user function or user-defined function) is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression.
A call specification declares a Java method or a third-generation language (3GL) routine so that it can be called from PL/SQL. You can also use the
CALL
SQL statement to call such a method or routine. The call specification tells Oracle Database which Java method, or which named function in which shared library, to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value. Oracle SQL Language Reference Create Function.
Oracle Syntax¶
For more information regarding Oracle Create Function, check here.
Oracle Create Function Syntax¶
CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ]
FUNCTION
[ schema. ] function_name
[ ( parameter_declaration [, parameter_declaration]... ) ] RETURN datatype
[ sharing_clause ]
[ { invoker_rights_clause
| accessible_by_clause
| default_collation_clause
| deterministic_clause
| parallel_enable_clause
| result_cache_clause
| aggregate_clause
| pipelined_clause
| sql_macro_clause
}...
]
{ IS | AS } { [ declare_section ]
BEGIN statement ...
[ EXCEPTION exception_handler [ exception_handler ]... ]
END [ name ] ;
|
{ java_declaration | c_declaration } } ;
Snowflake Syntax¶
Snowflake allows 3 different languages in their user-defined functions:
SQL
JavaScript
Java
For now, SnowConvert AI will support only SQL
and JavaScript
as target languages.
For more information regarding Snowflake Create Function, check here.
SQL¶
Note
SQL user-defined functions only support one query as their body. They can read from the database but are not allowed to write to or modify it (Scalar SQL UDFs).
CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
[ [ NOT ] NULL ]
[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
[ VOLATILE | IMMUTABLE ]
[ COMMENT = '<string_literal>' ]
AS '<function_definition>'
JavaScript¶
Note
JavaScript user-defined functions allow multiple statements in their bodies but cannot perform queries to the database. (Scalar JavaScript UDFs).
CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
[ [ NOT ] NULL ]
LANGUAGE JAVASCRIPT
[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
[ VOLATILE | IMMUTABLE ]
[ COMMENT = '<string_literal>' ]
AS '<function_definition>'
Sample Source Patterns¶
Sample auxiliary data¶
Note
This code was executed for a better understanding of the examples:
Oracle¶
CREATE TABLE table1 (col1 int, col2 int, col3 varchar2(250), col4 varchar2(250), col5 date);
INSERT INTO table1 VALUES (1, 11, 'val1_1', 'val1_2', TO_DATE('2004/05/03', 'yyyy-MM-dd'));
INSERT INTO table1 VALUES (2, 22, 'val2_1', 'val2_2', TO_DATE('2014/05/03', 'yyyy-MM-dd'));
INSERT INTO table1 VALUES (3, 33, 'val3_1', 'val3_2', TO_DATE('2024/05/03', 'yyyy-MM-dd'));
Snowflake¶
CREATE OR REPLACE TABLE table1 (col1 int,
col2 int,
col3 VARCHAR(250),
col4 VARCHAR(250),
col5 TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "07/25/2024" }}'
;
INSERT INTO table1
VALUES (1, 11, 'val1_1', 'val1_2', TO_DATE('2004/05/03', 'yyyy-MM-dd'));
INSERT INTO table1
VALUES (2, 22, 'val2_1', 'val2_2', TO_DATE('2014/05/03', 'yyyy-MM-dd'));
INSERT INTO table1
VALUES (3, 33, 'val3_1', 'val3_2', TO_DATE('2024/05/03', 'yyyy-MM-dd'));
Known Issues¶
No issues were found.
Cursor for a return variable¶
Note
Some parts in the output code are omitted for clarity reasons.
This pattern defines a function in Oracle PL/SQL that uses a cursor to fetch a single value and return it.
Components:
Function Declaration:
CREATE FUNCTION functionName(parameters) RETURN returnType
Declares the function with input parameters and the return type.
Variable Declarations:
Declares variables, including the return variable.
Cursor Declaration:
CURSOR cursorName IS SELECT singleColumn FROM ... WHERE ... [AND col1 = localVar1];
Defines a cursor to select a single column from a table with optional filtering conditions.
BEGIN-END Block:
Variables assignment.
Opens the cursor.
Fetch the result into the return variable.
Closes the cursor.
Returns the fetched value.
In this case, the variables are transformed into a common table expression (CTE). As well as the query within the cursor to which, in addition, the FETCH FIRST 1 ROW ONLY
clause is added to simulate the FETCH CURSOR
behavior.
RETURN
statement is transformed to the final select.
Queries¶
Oracle¶
CREATE OR REPLACE FUNCTION func1 (
company_ IN VARCHAR2,
book_id_ IN DATE,
object_id_ IN VARCHAR2 ) RETURN INTEGER
IS
temp_ table1.col2%TYPE;
CURSOR get_attr IS
SELECT col2
FROM table1
WHERE col3 = company_
AND col4 = object_id_
AND col5 = book_id_;
BEGIN
OPEN get_attr;
FETCH get_attr INTO temp_;
CLOSE get_attr;
RETURN temp_;
END func1;
Snowflake¶
CREATE OR REPLACE FUNCTION func1 (company_ VARCHAR, book_id_ TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/, object_id_ VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "09/06/2024" }}'
AS
$$
WITH declaration_variables_cte1 AS
(
SELECT
(
SELECT col2
FROM table1
WHERE col3 = company_
AND col4 = object_id_
AND col5 = book_id_
FETCH FIRST 1 ROW ONLY) AS temp_
)
SELECT
temp_
FROM
declaration_variables_cte1
$$;
Result¶
FUNC1() |
---|
2004-05-03. |
Oracle¶
CREATE FUNCTION func2 (
fa_period_ IN NUMBER,
to_date_ IN DATE DEFAULT NULL,
from_date_ IN DATE DEFAULT NULL ) RETURN NUMBER
IS
value_ NUMBER;
cond_date_to_ DATE;
cond_date_from_ DATE;
CURSOR get_acq_value IS
SELECT NVL(SUM(col1),0)
FROM table1
WHERE col3 IN (DECODE(fa_period_, 1, 'val1_1', 'val2_1'))
AND col5 <= cond_date_to_
AND col5 >= cond_date_from_;
BEGIN
value_ := 0;
cond_date_to_ := Get_Cond_Date( to_date_, 'MAX' );
cond_date_from_ := Get_Cond_Date( from_date_, 'MIN' );
OPEN get_acq_value;
FETCH get_acq_value INTO value_;
CLOSE get_acq_value;
RETURN (NVL(value_,0));
END func2;
Snowflake¶
CREATE OR REPLACE FUNCTION func2 (fa_period_ NUMBER(38, 18),
to_date_ TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/ DEFAULT NULL,
from_date_ TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/ DEFAULT NULL )
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "09/06/2024" }}'
AS
$$
WITH declaration_variables_cte1 AS
(
SELECT
0 AS
value_,
Get_Cond_Date( to_date_, 'MAX' ) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Get_Cond_Date' NODE ***/!!! AS
cond_date_to_,
Get_Cond_Date( from_date_, 'MIN' ) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Get_Cond_Date' NODE ***/!!! AS
cond_date_from_
),
declaration_variables_cte2 AS
(
SELECT
(
SELECT NVL(SUM(col1),0)
FROM table1
WHERE col3 IN (DECODE(fa_period_, 1, 'val1_1', 'val2_1'))
AND col5 <= cond_date_to_
AND col5 >= cond_date_from_
FETCH FIRST 1 ROW ONLY) AS value_,
cond_date_to_,
cond_date_from_
FROM
declaration_variables_cte1
)
SELECT
(NVL(value_,0))
FROM
declaration_variables_cte2
$$;
Result¶
FUNC1() |
---|
2004-05-03. |
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.
Cursor with IF statement¶
Note
Some parts in the output code are omitted for clarity reasons.
This pattern defines a function that conditionally uses a cursor to fetch and return a value based on an IF
statement.
Components:
Function Declaration:
CREATE FUNCTION functionName(parameters) RETURN returnType
Declares the function with input parameters and the return type.
Cursor Declaration:
CURSOR cursorName IS SELECT singleColumn FROM ... WHERE ... [AND col1 = localVar1];
Defines a cursor to select a single column from a table with optional filtering conditions.
Variable Declaration:
Declares variables, including the return variable.
BEGIN-END Block with IF Statement:
Variables assignment.
Check if a condition is true.
If true, opens the cursor, fetches the result into the return variable, closes the cursor, and returns the fetched value. (The cursor can also be opened in the
ELSE
block and must meet the same conditions)The
ELSE
Block is optional, if it exists, it should only contain a single statement that can be an assignment or aRETURN
statement.
The variables are transformed into a common table expression (CTE). As well as the query within the cursor to which, in addition, the FETCH FIRST 1 ROW ONLY
clause is added to simulate the FETCH CURSOR
behavior.
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..
Queries¶
Oracle¶
CREATE OR REPLACE FUNCTION func1 (
company_ IN NUMBER) RETURN NUMBER
IS
CURSOR getmaxperiod IS
SELECT max(col2)
FROM table1;
max_period_ NUMBER := 12;
BEGIN
IF 1 = 1 THEN
OPEN getmaxperiod;
FETCH getmaxperiod INTO max_period_ ;
CLOSE getmaxperiod;
RETURN max_period_;
ELSE
RETURN NULL;
END IF;
END func1;
Snowflake¶
CREATE OR REPLACE FUNCTION func1 (company_ NUMBER(38, 18))
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "09/06/2024" }}'
AS
$$
WITH declaration_variables_cte0 AS
(
SELECT
12 AS
max_period_
),
declaration_variables_cte1 AS
(
SELECT
CASE
WHEN 1 = 1
THEN (
SELECT max(col2)
FROM table1
FETCH FIRST 1 ROW ONLY)
ELSE NULL
END AS max_period_
FROM
declaration_variables_cte0
)
SELECT
max_period_
FROM
declaration_variables_cte1
$$;
Result¶
FUNC2(0) |
---|
NULL |
FUNC2(1) |
---|
33 |
Oracle¶
CREATE OR REPLACE FUNCTION func2(
company_ IN NUMBER) RETURN NUMBER
IS
CURSOR getmaxperiod IS
SELECT max(col2)
FROM table1;
max_period_ NUMBER := 1;
BEGIN
max_period_:= 2;
IF company_ = 1 THEN
RETURN max_period_ * 2;
ELSE
OPEN getmaxperiod;
FETCH getmaxperiod INTO max_period_ ;
CLOSE getmaxperiod;
RETURN max_period_;
END IF;
END func2;
Snowflake¶
CREATE OR REPLACE FUNCTION func2 (company_ NUMBER(38, 18))
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "09/06/2024" }}'
AS
$$
WITH declaration_variables_cte0 AS
(
SELECT
1 AS
max_period_
),
declaration_variables_cte1 AS
(
SELECT
2 AS
max_period_
FROM
declaration_variables_cte0
),
declaration_variables_cte2 AS
(
SELECT
CASE
WHEN company_ = 1
THEN max_period_ * 2
ELSE (
SELECT max(col2)
FROM table1
FETCH FIRST 1 ROW ONLY)
END AS max_period_
FROM
declaration_variables_cte1
)
SELECT
max_period_
FROM
declaration_variables_cte2
$$;
Result¶
FUNC2(0) |
---|
33 |
FUNC2(1) |
---|
2 |
Oracle¶
CREATE OR REPLACE FUNCTION func3 (
company_ IN NUMBER) RETURN NUMBER
IS
CURSOR getmaxperiod IS
SELECT max(col2)
FROM table1;
max_period_ NUMBER := 0;
BEGIN
IF company_ = 1 THEN
OPEN getmaxperiod;
FETCH getmaxperiod INTO max_period_ ;
CLOSE getmaxperiod;
END IF;
RETURN max_period_;
END func10;
Snowflake¶
CREATE OR REPLACE FUNCTION func3 (company_ NUMBER(38, 18))
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "09/06/2024" }}'
AS
$$
WITH declaration_variables_cte0 AS
(
SELECT
0 AS
max_period_
),
declaration_variables_cte1 AS
(
SELECT
CASE
WHEN company_ = 1
THEN (
SELECT max(col2)
FROM table1
FETCH FIRST 1 ROW ONLY)
ELSE max_period_
END AS max_period_
FROM
declaration_variables_cte0
)
SELECT
max_period_
FROM
declaration_variables_cte1
$$;
Result¶
FUNC2(0) |
---|
0 |
FUNC2(1) |
---|
33 |
Known Issues¶
No issues were found.
Related EWIS¶
No EWIs related.
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.
Oracle¶
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;
Snowflake¶
CREATE OR REPLACE FUNCTION Case1 (in_date_ TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/, min_max_ VARCHAR)
RETURNS TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "09/06/2024" }}'
AS
$$
WITH declaration_variables_cte0 AS
(
SELECT
CURRENT_DATE AS
cond_date_
),
declaration_variables_cte1 AS
(
SELECT
CASE
WHEN ( in_date_ IS NULL )
THEN CASE
WHEN ( min_max_ = 'MIN' )
THEN FOO1() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO1' NODE ***/!!!
ELSE FOO2() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO2' NODE ***/!!!
END
ELSE TRUNC(in_date_, 'DD')
END AS cond_date_
FROM
declaration_variables_cte0
)
SELECT
cond_date_
FROM
declaration_variables_cte1
$$;
Oracle¶
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;
Snowflake¶
CREATE OR REPLACE FUNCTION Case2 (year_ NUMBER(38, 18), id NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "09/06/2024" }}'
AS
$$
WITH declaration_variables_cte1 AS
(
SELECT
FOO1(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO1' NODE ***/!!! AS
base_value_,
FOO2(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO2' NODE ***/!!! AS
acc_depr_,
FOO3(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO3' NODE ***/!!! AS
acc_depr_prev_,
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN NUMBER AND unknown ***/!!!
NVL(base_value_,0) -(acc_depr_ + acc_depr_prev_) AS
residual_value_,
CASE
WHEN (residual_value_=0 AND base_value_!=0)
THEN 'TRUE'
ELSE 'FALSE'
END AS fully_depritiated_
)
SELECT
fully_depritiated_
FROM
declaration_variables_cte1
$$;
Oracle¶
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;
Snowflake¶
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "FOO1", "FOO2", "FOO3" **
CREATE OR REPLACE FUNCTION Case2_1 (year_ NUMBER(38, 18), id NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "07/16/2025", "domain": "no-domain-provided" }}'
AS
$$
WITH declaration_variables_cte1 AS
(
SELECT
FOO1(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO1' NODE ***/!!! AS
base_value_,
FOO2(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO2' NODE ***/!!! AS
acc_depr_,
FOO3(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO3' NODE ***/!!! AS
acc_depr_prev_,
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN NUMBER AND unknown ***/!!!
NVL(base_value_,0) -(acc_depr_ + acc_depr_prev_) AS
residual_value_,
CASE
WHEN (residual_value_=0 AND base_value_!=0)
THEN 'TRUE'
ELSE 'FALSE'
END AS fully_depritiated_,
NVL(fully_depritiated :: STRING, '') || ' CONCAT FOR TESTING' AS
fully_depritiated
),
declaration_variables_cte2 AS
(
SELECT
NVL(fully_depritiated :: STRING, '') || ' CONCAT FOR TESTING2' AS
fully_depritiated,
base_value_,
acc_depr_,
acc_depr_prev_,
residual_value_
FROM
declaration_variables_cte1
)
SELECT
fully_depritiated_
FROM
declaration_variables_cte2
$$;
Oracle¶
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;
Snowflake¶
CREATE OR REPLACE FUNCTION Case2_1 (year_ NUMBER(38, 18), id NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "09/06/2024" }}'
AS
$$
WITH declaration_variables_cte1 AS
(
SELECT
FOO1(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO1' NODE ***/!!! AS
base_value_,
FOO2(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO2' NODE ***/!!! AS
acc_depr_,
FOO3(year_, id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FOO3' NODE ***/!!! AS
acc_depr_prev_,
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN NUMBER AND unknown ***/!!!
NVL(base_value_,0) -(acc_depr_ + acc_depr_prev_) AS
residual_value_,
CASE
WHEN (residual_value_=0 AND base_value_!=0)
THEN 'TRUE'
ELSE 'FALSE'
END AS fully_depritiated_,
NVL(fully_depritiated :: STRING, '') || ' CONCAT FOR TESTING' AS
fully_depritiated
),
declaration_variables_cte2 AS
(
SELECT
NVL(fully_depritiated :: STRING, '') || ' CONCAT FOR TESTING2' AS
fully_depritiated,
base_value_,
acc_depr_,
acc_depr_prev_,
residual_value_
FROM
declaration_variables_cte1
)
SELECT
fully_depritiated_
FROM
declaration_variables_cte2
$$;
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.