Welcome to the SnowConvert for Teradata Lab 3. The main purpose of this lab is to guide you on the related tasks to take the Functions migrated using the SnowConvert Teradata tool, how to deploy them and some general guidance on how to finish any issues that required some manual revision.
A function is a subprogram that is used to return a single value.
They can be simple expressions like:
create or replace function adder(n1 in number, n2 in number) return number is n3 number(8); begin n3 :=n1+n2; return n3; end;
or leverage some SQL
CREATE OR REPLACE FUNCTION totalCustomers RETURN number IS total number(2) := 0; BEGIN SELECT count(*) into total FROM customers; RETURN total; END;
Snowflake allows you to create UDFs. In snowflake there are two types of UDFs
For more details check Snowflake Doc
A SQL UDF evaluates an arbitrary SQL expression and returns the results of the expression. The function definition can be a SQL expression that returns either a scalar (i.e. single) value or, if defined as a table function, a set of rows. The expression can be a query expression, though for non-tabular functions, the query expression must be guaranteed to return at most one row, containing a single column. The expression defining a UDF can refer to the input arguments of the function, and to database objects such as tables, views, and sequences. The UDF owner must have appropriate privileges on any database objects that the UDF accesses. A SQL UDF's defining expression can refer to other user-defined functions, though it cannot refer recursively to itself, either directly or through another function calling back to it.
Create the table and data to use:
create table purchases (number_sold integer, wholesale_price number(7,2), retail_price number(7,2)); insert into purchases (number_sold, wholesale_price, retail_price) values (3, 10.00, 20.00), (5, 100.00, 200.00) ;
Create the UDF:
create function profit() returns numeric(11, 2) as $$ select sum((retail_price - wholesale_price) * number_sold) from purchases $$;
Call the UDF in a query:
+----------+ | PROFIT() | |----------| | 530.00 | +----------+
Let's try a very quick an simple example:
nodejs it will probably easier.
We usually recommend that you start a JS terminal and try your code there. It could be with
nodejs or just using your browser.
For example open a terminal and type:
That will start the command line REPL. At that termina you can try some JS code. Once it works there. You can then copy and paste that code in your Snowflake Function.
RGB Encoding into HEX
// Converts the values of RGB components to a hexadecimal color code. /*Convert given RGB parameters to hexadecimal string using bitwise left-shift operator (<<) and Number.prototype.toString(16). Use String.prototype.padStart(6, '0') to get a 6-digit hexadecimal value.*/ const RGBToHex = (r, g, b) => ((r << 16) + (g << 8) + b).toString(16).padStart(6, '0'); // And to run it RGBToHex(255, 165, 1); // 'ffa501'
// Creates an array of partial sums. /*Use Array.prototype.reduce(), initialized with an empty array accumulator to iterate over nums. Use Array.prototype.slice(-1), the spread operator (...) and the unary + operator to add each value to the accumulator array containing the previous sums.*/ const accumulate = (...nums) => nums.reduce((acc, n) => [...acc, n + +acc.slice(-1)], ); accumulate(1, 2, 3, 4); // [1, 3, 6, 10] accumulate(...[1, 2, 3, 4]); // [1, 3, 6, 10]