INLINE TABLE-VALUED
Translation reference to convert Transact-SQL UDF (User Defined Functions) with TABLE return type to Snowflake.
Last updated
Translation reference to convert Transact-SQL 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
: 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