The 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
Select and return values directly from one table
This is the simplest scenario, performing a simple select from a table and returning those values
SQL Server
IN -> SqlServer_01.sql
CREATE FUNCTION GetDepartmentInfo()RETURNS TABLEASRETURN(SELECT DepartmentID, Name, GroupNameFROM HumanResources.Department);GOSELECT*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|
Snowflake SQL
OUT -> SqlServer_01.sql
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$$SELECTCAST(DepartmentID AS STRING),CAST(NameAS STRING),CAST(GroupName AS STRING)FROM HumanResources.Department$$;SELECT*fromTABLE(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|
Select and return values from multiple tables renaming columns and using built in functions
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.