SnowConvert - Translation Spec
  • For Teradata
  • Translation Reference
    • Teradata
      • Scripts to Snowflake SQL Translation Reference
        • COMMON STATEMENTS
          • ERROR HANDLING
          • EXIT or QUIT
          • GOTO
          • IF... THEN...
        • BTEQ
        • MLOAD
          • Import
      • Scripts To Python Translation Reference
        • BTEQ
          • REPEAT
          • USING REQUEST MODIFIER
        • FLOAD
          • BEGIN LOADING
        • MLOAD
          • BEGIN MLOAD
        • TPT
        • SnowConvert Scripts Helpers
          • Technical Documentation
      • SQL Translation Reference
        • Built-in Functions
          • CURRENT_TIMESTAMP
          • Cast to DATE using { }
          • Cast to INTERVAL datatype
          • COALESCE
          • DAYNUMBER_OF_MONTH
          • FROM_BYTES
          • GETQUERYBANDVALUE
          • JSON_CHECK
          • JSON_EXTRACT
          • JSON_TABLE
          • NEW JSON
          • NVP
          • OVERLAPS
          • PIVOT
          • P_INTERSECT
          • RANK
          • Regex functions
          • STRTOK_SPLIT_TO_TABLE
          • SUBSTRING
          • TD_UNPIVOT
          • TO_CHAR
          • XMLAGG
        • Database DBC
        • Data Types
        • DDL
          • Tables
            • WITH DEFAULT
          • Index
          • Views
        • DML
          • Delete Statement
          • Insert Statement
            • LOGGING ERRORS
          • Select Statement
            • Expand On Clause
            • Normalize
            • Reset When
          • Set Operators
          • Update Statement
          • With Modifier
        • SnowConvert Procedures Helpers
          • Cursor Helper
          • Exec Helper
          • Functional Equivalence Helpers
          • Into Helper
      • Teradata to JavaScript Translation Reference
        • GET DIAGNOSTICS EXCEPTION
        • Macros
        • Procedures
      • Teradata to Snowflake Scripting Translation Reference
        • ABORT and ROLLBACK
        • BEGIN END
        • CASE
        • CREATE MACRO
        • CREATE PROCEDURE
        • CURSOR
        • DECLARE
        • DECLARE CONDITION HANDLER
        • DML and DDL Objects
        • EXCEPTION HANDLERS
        • EXECUTE IMMEDIATE
        • EXECUTE/EXEC
        • FUNCTION OPTIONS OR DATA ACCESS
        • GET DIAGNOSTICS EXCEPTION
        • IF
        • LOCKING FOR ACCESS
        • LOOP
        • OUTPUT PARAMETERS
        • PREPARE
        • REPEAT
        • SET
        • SYSTEM_DEFINED
        • WHILE
    • Oracle
      • Sample data
      • Basic Elements of Oracle SQL
        • Data Types
          • Oracle Built-in Data Types
            • Character Data Types
              • CHAR Data type
              • NCHAR Data Type
              • VARCHAR2 Data Type
              • VARCHAR Data Type
              • NVARCHAR2 Data Type
            • Numeric Data Types
              • NUMBER Data Type
              • FLOAT Data Type
              • Floating-Point Numbers
                • BINARY_FLOAT
                • BINARY_DOUBLE
            • LONG Data Type
            • Datetime and Interval Data Types
              • DATE Data Type
              • TIMESTAMP Data Type
              • TIMESTAMP WITH TIME ZONE Data Type
              • TIMESTAMP WITH LOCAL TIME ZONE Data Type
              • INTERVAL YEAR TO MONTH Data Type
              • INTERVAL DAY TO SECOND Data Type
              • Datetime Arithmetic
                • Interval UDFs vs Snowflake native interval operation
            • LOB Data Types
              • BFILE Data Type
              • BLOB Data Type
              • CLOB Data Type
              • NCLOB Data type
            • JSON Data Type
            • Extended Data Types
            • RAW and LONG RAW Data types
            • PL SQL Data Types
              • PLS_INTEGER Data Type
              • BINARY_INTEGER Data Type
          • Rowid Data Type
            • ROWID DataType
            • UROWID Data Type
          • ANSI Data Types
          • User-Defined Types
            • REF Data Types
          • Any Types
            • ANYTYPE
            • ANYDATA
            • ANYDATASET
          • XML Types
            • XMLType
            • URI Data Types
              • HTTPURIType
              • XDBURIType
              • DBURIType
            • URIFactory Package
          • Spatial Types
            • SDO_GEOMETRY
            • SDO_TOPO_GEOMETRY
            • SDO_GEORASTER
        • Literals
          • Interval Literal
          • Interval Type and Date Type
          • Text literals
      • Pseudocolumns
        • ROWID
        • ROWNUM
      • Built-in functions
        • SnowConvert Custom UDFs
          • BFILENAME UDF
          • DATE_TO_JULIANDAYS_UDF
          • DATEADD UDF
          • DATEDIFF UDF
          • INTERVAL UDFs
            • DATEADD UDF INTERVAL
            • DATEDIFF UDF INTERVAL
          • CAST_DATE UDF
          • JSON_VALUE UDF
          • JULIAN TO GREGORIAN DATE UDF
          • MONTHS BETWEEN UDF [DEPRECATED]
          • REGEXP LIKE UDF
          • TIMESTAMP DIFF UDF
          • TRUNC (date) UDF
          • TRUNC (number) UDF
        • TO_NUMBER
        • NLSSORT
      • Built-In packages
        • DBMS_LOB
          • SUBSTR Function
        • DBMS_RANDOM
          • VALUE functions
        • DBMS_OUTPUT
          • PUT_LINE procedure
        • UTL_FILE
          • FOPEN procedure
          • PUT_LINE procedure
          • FCLOSE procedure
      • SQL Queries and Subqueries
        • Select
          • Select Flashback Query
        • Joins
          • Equijoin
          • Band Join
          • Self Join
          • Cartesian Products
          • Inner Join
          • Outer Join
          • Antijoin
          • Semijoin
      • SQL Statements
        • Alter Session
        • Alter Table
        • Create Materialized Views
        • Create Database Link
        • Create Index
        • Create Sequence
        • Create Synonym
        • Create Table
        • Create Type
          • Object Type Definition
          • Subtype Definition
          • Array Type Definition
          • Nested Table Type Definition
          • Member Function Definitions
        • Create View
        • Drop Table
      • PL/SQL to Snowflake Scripting
        • ASSIGNMENT STATEMENT
        • CALL
        • CASE
        • COLLECTIONS AND RECORDS
          • Associative Array Type Definition
          • Varray Type Definition
          • Nested Table Array Type Definition
          • Collection Methods
          • Collection Bulk Operations
            • WITH, SELECT, and BULK COLLECT INTO statements
          • Record Type Definition
        • COMPOUND STATEMENTS
        • CONTINUE
        • CREATE PROCEDURE
        • CURSOR
          • PARAMETRIZED CURSOR
          • CURSOR DECLARATION
          • Workaround for cursors using parameters or procedure variables
          • Cursor Variables
        • DECLARE
        • DEFAULT PARAMETERS
        • DML STATEMENTS
          • INSERT Statement Extension
          • MERGE Statement
          • SELECT INTO Statement
          • Work around to simulate the use of Records
        • EXIT
        • EXPRESSIONS
        • EXECUTE IMMEDIATE
        • FORALL
        • FOR LOOP
        • HELPERS
          • Bulk Cursor Helpers
        • IF
        • IS EMPTY
        • LOCK TABLE
        • LOG ERROR
        • LOOP
        • OUTPUT PARAMETERS
        • PACKAGES
          • DECLARATION
          • BODY
          • VARIABLES
          • Constants
        • PROCEDURE CALL
        • RAISE
        • RAISE_APPICATION_ERROR
        • UDF CALL
        • WHILE
      • PL/SQL to Javascript
        • Procedures
        • User defined functions
        • Packages
        • Helpers
          • EXEC Helper
          • Cursor Helper
          • Raise Helper
          • ROWTYPE Helper
          • Between operator helper
          • Like operator Helper
          • IS NULL Helper
          • Concat Value Helper
          • Package variables helper
          • Implicit Cursor attribute helper
        • Declarations
        • Control Statements
        • Conditional Compilation
        • Collections & Records
        • DDL - DML Statements
        • SQL Language Elements
        • Expressions and operators
        • Synonyms
        • Triggers
        • TYPE attribute
      • SQL*Plus
        • Archive Log
        • Attribute
        • Break
        • Btitle
        • Change
        • Column
        • Define
        • Host
        • Prompt
        • Remark
        • Set
        • Spool
        • Start
        • Whenever oserror
        • Whenever sqlerror
        • Show
        • Append
        • Accept
      • Wrapped objects
    • SQLServer
      • General Language Elements
        • COMPUTED COLUMN
        • EXECUTE
          • System Store Procedures
            • SP_RENAME
        • Collate
        • USE
        • OUTER APPLY
      • DDLs
        • Tables
        • Index
        • Views
        • Procedures
        • Functions
      • DMLs
        • Set Operators
        • Between
        • Update
        • Select
        • Insert
        • Delete
        • Merge
        • Exists
        • IN
        • Truncate
        • Drop
        • Bulk Insert
        • Common Table Expression (CTE)
        • Drops
      • Data Types
      • Statements
        • ALTER
          • TABLE
            • ADD
              • COLUMN DEFINITION
                • COLUMN CONSTRAINT
                  • FOREIGN KEY
                  • PRIMARY KEY / UNIQUE
                  • CHECK
              • TABLE CONSTRAINT
                • FOREIGN KEY
                • PRIMARY KEY
                • CHECK CONSTRAINT
                • CHECK
                • CONNECTION
                • DEFAULT
                • ON PARTITION
        • CREATE
          • FUNCTION
            • SCALAR
            • INLINE TABLE-VALUED
            • MULTI-STATEMENT TABLE-VALUED
      • Built-in functions
        • SnowConvert custom UDFs
          • OPENXML UDF
          • STR UDF
          • SWITCHOFFSET_UDF
        • Aggregate functions
          • COUNT
          • COUNT_BIG
          • SUM
        • Analytic Functions
          • LAG
        • Conversion functions
          • CONVERT
          • TRY_CONVERT
        • Data Type functions
          • DATALENGTH
        • Date & Time functions
          • AT TIME ZONE
          • SWITCHOFFSET
          • DATEADD
          • DATEDIFF
          • DATEPART
          • DATEFROMPARTS
          • DATENAME
          • DAY
          • EOMONTH
          • GETDATE
          • MONTH
          • SYSDATETIME
          • SYSUTCDATETIME
          • YEAR
        • Logical functions
          • IIF
        • Mathematical functions
          • ABS
          • ACOS
            • ACOS in JS
          • ASIN
            • ASIN in JS
          • ATAN
            • ATAN in JS
          • ATN2
            • ATAN2 in JS
          • AVG
          • CEILING
          • COS
            • COS in JS
          • COT
            • COT in JS
          • DEGREES
            • DEGREES in JS
          • EXP
            • EXP in JS
          • FLOOR
          • LOG
            • LOG in JS
          • LOG10
            • LOG10 in JS
          • PI
            • PI in JS
          • POWER
            • POW in JS
          • SQUARE
          • STDEV
          • STDEVP
          • VAR
          • POWER
          • RADIANS
            • RADIANS in JS
          • ROUND
          • SQRT
        • Metadata functions
          • DB_NAME
          • OBJECT_ID
        • Ranking functions
          • DENSE_RANK
          • RANK
          • ROW_NUMBER
        • String functions
          • ASCII
            • ASCII in JS
          • CHAR
          • CHARINDEX
          • COALESCE
          • CONCAT
          • CONCAT_WS
            • Join in JS
          • DIFFERENCE
            • DIFFERENCE in JS
          • FORMAT
            • FORMAT in JS
          • LEFT
          • LEN
          • LOWER
          • LTRIM
            • LTRIM in JS
          • NCHAR
          • PATINDEX
            • search in JS
          • QUOTENAME
            • QUOTENAME in JS
          • REPLACE
          • REPLICATE
          • REVERSE
            • reverse in JS
          • RIGHT
          • RTRIM
          • SOUNDEX
            • SOUNDEX in JS
          • SPACE
          • STR
            • STR in JS
          • STRING_ESCAPE
            • stringify in JS
          • SUBSTRING
          • TRIM
            • trim in JS
          • UPPER
        • System functions
          • FORMATMESSAGE
            • FORMATMESSAGE_UDF
          • ISNULL
          • NEWID
          • NULLIF
        • XML Functions
          • Value
          • Query
      • Built-in procedures
        • Custom User Defined Procedures
          • SP_ADDEXTENDEDPROPERTY_UDP
      • Snowflake Scripting
        • CREATE PROCEDURE
        • CASE
        • CURSOR
        • DECLARE
        • EXECUTE
        • IF
        • SET
        • DMLs
        • CALL
        • WHILE
        • DROPs
        • BEGIN and COMMIT Transaction
        • OUTPUT PARAMETERS
        • LABEL and GOTO
        • SELECT
        • TEXTIMAGE_ON
        • TRY CATCH
      • System Tables
        • SYS.FOREIGN_KEYS
      • Queries
        • TOP
Powered by GitBook
On this page
  • Description
  • SQL Server Syntax
  • Snowflake SQL Syntax
  • Sample Source Patterns
  • Select and return values directly from one table
  • Select and return values from multiple tables renaming columns and using built in functions
  • Select columns using WITH statement
  • Known issues
  • Related EWIs
  1. Translation Reference
  2. SQLServer
  3. Statements
  4. CREATE
  5. FUNCTION

INLINE TABLE-VALUED

Translation reference to convert SQL Server UDF (User Defined Functions) with TABLE return type to Snowflake.

PreviousSCALARNextMULTI-STATEMENT TABLE-VALUED

Last updated 1 year ago

Description

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 ().

SQL Server Syntax

-- 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 [ ) ]
[ ; ]

Snowflake SQL Syntax

CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
  RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
  AS '<sql_expression>'sql

Sample Source Patterns

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 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|

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
$$
    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|

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.

SQL Server

IN -> SqlServer_02.sql
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|    

Snowflake SQL

OUT -> SqlServer_02.sql
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|

Select columns using WITH statement

The body of an inline table-valued function can also be specified using a WITH statement as shown below.

SQL Server

IN -> SqlServer_03.sql
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                |

Snowflake SQL

OUT -> SqlServer_03.sql
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                |

Known issues

No issues were found

Related EWIs

  1. 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

SQL Server Language Reference Creating an inline table-valued function
SSC-PRF-TS0001
SSC-EWI-0073