INLINE TABLE-VALUED
Translation reference to convert SQL Server UDF (User Defined Functions) with TABLE return type to Snowflake.
Last updated
Translation reference to convert SQL Server UDF (User Defined Functions) with TABLE return type to Snowflake.
Last updated
Some parts in the output code are omitted for clarity reasons.
Inline Table-Valued functions are table expression that can accept parameters, perform a SELECT statement and return a TABLE ().
-- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
AS '<sql_expression>'sqlThe following section describes all the possible source code patterns that can appear in this kind of CREATE FUNCTION syntax.
For Inline Table-Valued functions, there can only exist one statement per body that could be:
SELECT Statement
WITH Common Table Expression
This is the simplest scenario, performing a simple select from a table and returning those values
CREATE FUNCTION GetDepartmentInfo()
RETURNS TABLE
AS
RETURN
(
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
);
GO
SELECT * from GetDepartmentInfo()DepartmentID|Name |GroupName |
------------+--------------------------+------------------------------------+
1|Engineering |Research and Development |
2|Tool Design |Research and Development |
3|Sales |Sales and Marketing |
4|Marketing |Sales and Marketing |
5|Purchasing |Inventory Management |
6|Research and Development |Research and Development |
7|Production |Manufacturing |
8|Production Control |Manufacturing |
9|Human Resources |Executive General and Administration|
10|Finance |Executive General and Administration|
11|Information Services |Executive General and Administration|
12|Document Control |Quality Assurance |
13|Quality Assurance |Quality Assurance |
14|Facilities and Maintenance|Executive General and Administration|
15|Shipping and Receiving |Inventory Management |
16|Executive |Executive General and Administration|CREATE OR REPLACE FUNCTION GetDepartmentInfo ()
RETURNS TABLE(
DepartmentID STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN DepartmentID WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
Name STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Name WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
GroupName STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN GroupName WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
SELECT
CAST(DepartmentID AS STRING),
CAST(Name AS STRING),
CAST(GroupName AS STRING)
FROM
HumanResources.Department
$$;
SELECT
*
from
TABLE(GetDepartmentInfo());DepartmentID|Name |GroupName |
------------+--------------------------+------------------------------------+
1|Engineering |Research and Development |
2|Tool Design |Research and Development |
3|Sales |Sales and Marketing |
4|Marketing |Sales and Marketing |
5|Purchasing |Inventory Management |
6|Research and Development |Research and Development |
7|Production |Manufacturing |
8|Production Control |Manufacturing |
9|Human Resources |Executive General and Administration|
10|Finance |Executive General and Administration|
11|Information Services |Executive General and Administration|
12|Document Control |Quality Assurance |
13|Quality Assurance |Quality Assurance |
14|Facilities and Maintenance|Executive General and Administration|
15|Shipping and Receiving |Inventory Management |
16|Executive |Executive General and Administration|This is an example of a query using built-in functions in a select statement getting data from different tables, renaming columns and returning a table.
CREATE FUNCTION GetPersonBasicInfo()
RETURNS TABLE
AS
RETURN
(
SELECT TOP (20)
P.PersonType,
P.FirstName,
E.JobTitle,
E.Gender,
YEAR(E.HireDate) as HIREYEAR
FROM
Person.Person P
INNER JOIN
HumanResources.Employee E
ON
P.BusinessEntityID = E.BusinessEntityID
);
GO
SELECT * FROM GetPersonBasicInfo();PersonType|FirstName|JobTitle |Gender|HIREYEAR|
----------+---------+---------------------------------+------+--------+
EM |Ken |Chief Executive Officer |M | 2009|
EM |Terri |Vice President of Engineering |F | 2008|
EM |Roberto |Engineering Manager |M | 2007|
EM |Rob |Senior Tool Designer |M | 2007|
EM |Gail |Design Engineer |F | 2008|
EM |Jossef |Design Engineer |M | 2008|
EM |Dylan |Research and Development Manager |M | 2009|
EM |Diane |Research and Development Engineer|F | 2008|
EM |Gigi |Research and Development Engineer|F | 2009|
EM |Michael |Research and Development Manager |M | 2009|
EM |Ovidiu |Senior Tool Designer |M | 2010|
EM |Thierry |Tool Designer |M | 2007|
EM |Janice |Tool Designer |F | 2010|
EM |Michael |Senior Design Engineer |M | 2010|
EM |Sharon |Design Engineer |F | 2011|
EM |David |Marketing Manager |M | 2007|
EM |Kevin |Marketing Assistant |M | 2007|
EM |John |Marketing Specialist |M | 2011|
EM |Mary |Marketing Assistant |F | 2011|
EM |Wanida |Marketing Assistant |F | 2011| CREATE OR REPLACE FUNCTION GetPersonBasicInfo ()
RETURNS TABLE(
PersonType STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN PersonType WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
FirstName STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN FirstName WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
JobTitle STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN JobTitle WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
Gender STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Gender WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
HIREYEAR INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
SELECT
TOP 20
CAST(P.PersonType AS STRING),
CAST(P.FirstName AS STRING),
CAST(E.JobTitle AS STRING),
CAST(E.Gender AS STRING),
YEAR(E.HireDate :: TIMESTAMP) as HIREYEAR
FROM
Person.Person P
INNER JOIN
HumanResources.Employee E
ON P.BusinessEntityID = E.BusinessEntityID
$$;
SELECT
*
FROM
TABLE(GetPersonBasicInfo());PersonType|FirstName|JobTitle |Gender|HIREYEAR|
----------+---------+---------------------------------+------+--------+
EM |Ken |Chief Executive Officer |M | 2009|
EM |Terri |Vice President of Engineering |F | 2008|
EM |Roberto |Engineering Manager |M | 2007|
EM |Rob |Senior Tool Designer |M | 2007|
EM |Gail |Design Engineer |F | 2008|
EM |Jossef |Design Engineer |M | 2008|
EM |Dylan |Research and Development Manager |M | 2009|
EM |Diane |Research and Development Engineer|F | 2008|
EM |Gigi |Research and Development Engineer|F | 2009|
EM |Michael |Research and Development Manager |M | 2009|
EM |Ovidiu |Senior Tool Designer |M | 2010|
EM |Thierry |Tool Designer |M | 2007|
EM |Janice |Tool Designer |F | 2010|
EM |Michael |Senior Design Engineer |M | 2010|
EM |Sharon |Design Engineer |F | 2011|
EM |David |Marketing Manager |M | 2007|
EM |Kevin |Marketing Assistant |M | 2007|
EM |John |Marketing Specialist |M | 2011|
EM |Mary |Marketing Assistant |F | 2011|
EM |Wanida |Marketing Assistant |F | 2011|The body of an inline table-valued function can also be specified using a WITH statement as shown below.
CREATE FUNCTION GetMaritalStatusByGender
(
@P_Gender nchar(1)
)
RETURNS TABLE
AS
RETURN
(
WITH CTE AS
(
SELECT BusinessEntityID, MaritalStatus, Gender
FROM HumanResources.Employee
where Gender = @P_Gender
)
SELECT
MaritalStatus, Gender, CONCAT(P.FirstName,' ', P.LastName) as Name
FROM
CTE INNER JOIN Person.Person P
ON
CTE.BusinessEntityID = P.BusinessEntityID
);
GO
select * from GetMaritalStatusByGender('F');MaritalStatus|Gender|Name |
-------------+------+-----------------------+
S |F |Terri Duffy |
M |F |Gail Erickson |
S |F |Diane Margheim |
M |F |Gigi Matthew |
M |F |Janice Galvin |
M |F |Sharon Salavaria |
S |F |Mary Dempsey |
M |F |Wanida Benshoof |
M |F |Mary Gibson |
M |F |Jill Williams |
S |F |Jo Brown |
M |F |Britta Simon |
M |F |Margie Shoop |
M |F |Rebecca Laszlo |
M |F |Suchitra Mohan |
M |F |Kim Abercrombie |
S |F |JoLynn Dobney |
M |F |Nancy Anderson |
M |F |Ruth Ellerbrock |
M |F |Doris Hartwig |
M |F |Diane Glimp |
M |F |Bonnie Kearney |
M |F |Denise Smith |
S |F |Diane Tibbott |
M |F |Carole Poland |
M |F |Carol Philips |
M |F |Merav Netz |
S |F |Betsy Stadick |
S |F |Danielle Tiedt |
S |F |Kimberly Zimmerman |
M |F |Elizabeth Keyser |
M |F |Mary Baker |
M |F |Alice Ciccu |
M |F |Linda Moschell |
S |F |Angela Barbariol |
S |F |Kitti Lertpiriyasuwat |
S |F |Susan Eaton |
S |F |Kim Ralls |
M |F |Nicole Holliday |
S |F |Anibal Sousa |
M |F |Samantha Smith |
S |F |Olinda Turner |
S |F |Cynthia Randall |
M |F |Sandra Reátegui Alayo |
S |F |Linda Randall |
S |F |Shelley Dyck |
S |F |Laura Steele |
S |F |Susan Metters |
S |F |Katie McAskill-White |
M |F |Barbara Decker |
M |F |Yvonne McKay |
S |F |Janeth Esteves |
M |F |Brenda Diaz |
M |F |Lorraine Nay |
M |F |Paula Nartker |
S |F |Lori Kane |
M |F |Kathie Flood |
S |F |Belinda Newman |
M |F |Karen Berge |
M |F |Lori Penor |
M |F |Jo Berry |
M |F |Laura Norman |
M |F |Paula Barreto de Mattos|
M |F |Mindy Martin |
M |F |Deborah Poe |
S |F |Candy Spoon |
M |F |Barbara Moreland |
M |F |Janet Sheperdigian |
S |F |Wendy Kahn |
S |F |Sheela Word |
M |F |Linda Meisner |
S |F |Erin Hagens |
M |F |Annette Hill |
S |F |Jean Trenary |
S |F |Stephanie Conroy |
S |F |Karen Berg |
M |F |Janaina Bueno |
M |F |Linda Mitchell |
S |F |Jillian Carson |
S |F |Pamela Ansman-Wolfe |
S |F |Lynn Tsoflias |
M |F |Amy Alberts |
S |F |Rachel Valdez |
M |F |Jae Pak |CREATE OR REPLACE FUNCTION GetMaritalStatusByGender
(P_GENDER STRING
)
RETURNS TABLE(
MaritalStatus STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN MaritalStatus WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
Gender STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Gender WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
Name VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
--** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
WITH CTE AS
(
SELECT
BusinessEntityID,
MaritalStatus,
Gender
FROM
HumanResources.Employee
where
Gender = :P_GENDER
)
SELECT
CAST(MaritalStatus AS STRING),
CAST(Gender AS STRING),
CONCAT(P.FirstName,' ', P.LastName) as Name
FROM
CTE
INNER JOIN
Person.Person P
ON CTE.BusinessEntityID = P.BusinessEntityID
$$;
select
*
from GetMaritalStatusByGender('F') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TableValuedFunctionCall' NODE ***/!!!;MaritalStatus|Gender|Name |
-------------+------+-----------------------+
S |F |Terri Duffy |
M |F |Gail Erickson |
S |F |Diane Margheim |
M |F |Gigi Matthew |
M |F |Janice Galvin |
M |F |Sharon Salavaria |
S |F |Mary Dempsey |
M |F |Wanida Benshoof |
M |F |Mary Gibson |
M |F |Jill Williams |
S |F |Jo Brown |
M |F |Britta Simon |
M |F |Margie Shoop |
M |F |Rebecca Laszlo |
M |F |Suchitra Mohan |
M |F |Kim Abercrombie |
S |F |JoLynn Dobney |
M |F |Nancy Anderson |
M |F |Ruth Ellerbrock |
M |F |Doris Hartwig |
M |F |Diane Glimp |
M |F |Bonnie Kearney |
M |F |Denise Smith |
S |F |Diane Tibbott |
M |F |Carole Poland |
M |F |Carol Philips |
M |F |Merav Netz |
S |F |Betsy Stadick |
S |F |Danielle Tiedt |
S |F |Kimberly Zimmerman |
M |F |Elizabeth Keyser |
M |F |Mary Baker |
M |F |Alice Ciccu |
M |F |Linda Moschell |
S |F |Angela Barbariol |
S |F |Kitti Lertpiriyasuwat |
S |F |Susan Eaton |
S |F |Kim Ralls |
M |F |Nicole Holliday |
S |F |Anibal Sousa |
M |F |Samantha Smith |
S |F |Olinda Turner |
S |F |Cynthia Randall |
M |F |Sandra Reátegui Alayo |
S |F |Linda Randall |
S |F |Shelley Dyck |
S |F |Laura Steele |
S |F |Susan Metters |
S |F |Katie McAskill-White |
M |F |Barbara Decker |
M |F |Yvonne McKay |
S |F |Janeth Esteves |
M |F |Brenda Diaz |
M |F |Lorraine Nay |
M |F |Paula Nartker |
S |F |Lori Kane |
M |F |Kathie Flood |
S |F |Belinda Newman |
M |F |Karen Berge |
M |F |Lori Penor |
M |F |Jo Berry |
M |F |Laura Norman |
M |F |Paula Barreto de Mattos|
M |F |Mindy Martin |
M |F |Deborah Poe |
S |F |Candy Spoon |
M |F |Barbara Moreland |
M |F |Janet Sheperdigian |
S |F |Wendy Kahn |
S |F |Sheela Word |
M |F |Linda Meisner |
S |F |Erin Hagens |
M |F |Annette Hill |
S |F |Jean Trenary |
S |F |Stephanie Conroy |
S |F |Karen Berg |
M |F |Janaina Bueno |
M |F |Linda Mitchell |
S |F |Jillian Carson |
S |F |Pamela Ansman-Wolfe |
S |F |Lynn Tsoflias |
M |F |Amy Alberts |
S |F |Rachel Valdez |
M |F |Jae Pak |No issues were found
SSC-FDM-TS0012: Information for the expression was not found. CAST to STRING used
: Performance warning - recursion for CTE not checked. Might require a recursive keyword.
: Pending Functional Equivalence Review