LogoLogo
SnowflakeDocumentation Home
  • Snowflake SnowConvert Documentation
  • General
    • About
    • Getting Started
      • System Requirements
      • Best practices
      • Download and Access
      • Code Extraction
        • Teradata
        • Oracle
        • SQL Server
        • Redshift
      • Running SnowConvert
        • Supported Languages
          • Teradata
          • Oracle
          • SQL Server
          • Redshift
          • Google BigQuery
          • Azure Synapse
          • Sybase IQ
          • PostgreSQL & Based Languages
        • Validation
          • Ambiguous Comments Validation
          • Extraction Validation
          • File Encoding Validation
          • File Extension Validation
          • File Format Validation
          • System Object Naming Validation
        • Assessment
          • Analyzing subfolders
        • Conversion
          • Converting subfolders
          • General Conversion Settings
          • Teradata Conversion Settings
          • Oracle Conversion Settings
          • Transact-SQL Conversion Settings
        • Review Results
          • SnowConvert Scopes
          • Output Code
          • Reports
            • Top-Level Code Units Report
            • Embedded Code Units Report
            • Assessment Report
              • Overall Conversion Summary
              • SQL Conversion Summary
              • Scripts Line Conversion Summary
              • Object Conversion Summary
              • File and Object Level Breakdown - SQL Files
              • File and Object Level Breakdown - SQL Identified Objects
              • Scripts - Files
              • Scripts - Identified Objects
              • Schemas
              • Databases & Schemas
              • Code Completeness Score
            • Issues Report
            • Missing Objects Report
            • Object References Report
            • Elements Report
            • Renaming Report
      • Training and Support
    • Conversion Software Terms of Use
      • Open Source Libraries
    • Release Notes
      • Recent Release Notes
      • Previous Release Notes By Platform
        • Teradata
          • Release Notes
            • 2024
            • 2023
            • 2022
            • 2021
            • 2020
            • Roadmap
        • Oracle
          • Release Notes
            • 2023
            • 2022
            • 2021
            • 2020
            • Roadmap
        • SQL Server
          • Release Notes
            • 2023
            • 2022
            • 2021
    • User Guide
      • SnowConvert
        • How to install the tool
          • Linux
          • Windows
          • MacOS
        • How to update the tool
        • How to get an access code
          • How to Retrieve Your UUID for SnowConvert Offline Activation in Linux
        • What is a SnowConvert Project?
        • How to use the SnowConvert CLI
        • Command Line Interface
          • Teradata
          • Oracle
          • Transact-SQL
          • Redshift
          • Renaming feature
      • SnowConvert for Redshift (Preview)
        • How to install the tool
          • Windows
          • MacOS
        • How to update the tool
        • How to inquire about an access code
        • What is a SnowConvert for Redshift project?
          • Project creation
          • Extraction
          • Conversion
          • Deployment
          • Data Migration
    • Technical Documentation
      • Understanding Converted Code
        • EWIs
          • General
            • SSC-EWI-0001
            • SSC-EWI-0002
            • SSC-EWI-0003
            • SSC-EWI-0004
            • SSC-EWI-0005
            • SSC-EWI-0006
            • SSC-EWI-0007
            • SSC-EWI-0008
            • SSC-EWI-0009
            • SSC-EWI-0010
            • SSC-EWI-0011
            • SSC-EWI-0012
            • SSC-EWI-0013
            • SSC-EWI-0014
            • SSC-EWI-0015
            • SSC-EWI-0020
            • SSC-EWI-0021
            • SSC-EWI-0022
            • SSC-EWI-0023
            • SSC-EWI-0025
            • SSC-EWI-0026
            • SSC-EWI-0027
            • SSC-EWI-0028
            • SSC-EWI-0030
            • SSC-EWI-0031
            • SSC-EWI-0033
            • SSC-EWI-0034
            • SSC-EWI-0035
            • SSC-EWI-0036
            • SSC-EWI-0040
            • SSC-EWI-0041
            • SSC-EWI-0045
            • SSC-EWI-0046
            • SSC-EWI-0049
            • SSC-EWI-0052
            • SSC-EWI-0053
            • SSC-EWI-0054
            • SSC-EWI-0056
            • SSC-EWI-0058
            • SSC-EWI-0062
            • SSC-EWI-0064
            • SSC-EWI-0066
            • SSC-EWI-0067
            • SSC-EWI-0068
            • SSC-EWI-0073
            • SSC-EWI-0077
            • SSC-EWI-0080
            • SSC-EWI-0084
            • SSC-EWI-0086
            • SSC-EWI-0092
            • SSC-EWI-0094
            • SSC-EWI-0101
            • SSC-EWI-0102
            • SSC-EWI-0107
            • SSC-EWI-0108
            • SSC-EWI-0109
            • SSC-EWI-0110
          • Teradata
            • SSC-EWI-TD0001
            • SSC-EWI-TD0002
            • SSC-EWI-TD0003
            • SSC-EWI-TD0004
            • SSC-EWI-TD0005
            • SSC-EWI-TD0006
            • SSC-EWI-TD0008
            • SSC-EWI-TD0009
            • SSC-EWI-TD0010
            • SSC-EWI-TD0012
            • SSC-EWI-TD0017
            • SSC-EWI-TD0020
            • SSC-EWI-TD0023
            • SSC-EWI-TD0024
            • SSC-EWI-TD0025
            • SSC-EWI-TD0027
            • SSC-EWI-TD0029
            • SSC-EWI-TD0031
            • SSC-EWI-TD0034
            • SSC-EWI-TD0039
            • SSC-EWI-TD0040
            • SSC-EWI-TD0041
            • SSC-EWI-TD0046
            • SSC-EWI-TD0049
            • SSC-EWI-TD0051
            • SSC-EWI-TD0052
            • SSC-EWI-TD0053
            • SSC-EWI-TD0055
            • SSC-EWI-TD0057
            • SSC-EWI-TD0059
            • SSC-EWI-TD0060
            • SSC-EWI-TD0061
            • SSC-EWI-TD0063
            • SSC-EWI-TD0066
            • SSC-EWI-TD0068
            • SSC-EWI-TD0069
            • SSC-EWI-TD0070
            • SSC-EWI-TD0076
            • SSC-EWI-TD0077
            • SSC-EWI-TD0079
            • SSC-EWI-TD0082
            • SSC-EWI-TD0083
            • SSC-EWI-TD0087
            • SSC-EWI-TD0091
          • Oracle
            • SSC-EWI-OR0001
            • SSC-EWI-OR0002
            • SSC-EWI-OR0004
            • SSC-EWI-OR0005
            • SSC-EWI-OR0006
            • SSC-EWI-OR0007
            • SSC-EWI-OR0008
            • SSC-EWI-OR0009
            • SSC-EWI-OR0010
            • SSC-EWI-OR0011
            • SSC-EWI-OR0013
            • SSC-EWI-OR0014
            • SSC-EWI-OR0016
            • SSC-EWI-OR0020
            • SSC-EWI-OR0023
            • SSC-EWI-OR0026
            • SSC-EWI-OR0029
            • SSC-EWI-OR0030
            • SSC-EWI-OR0031
            • SSC-EWI-OR0032
            • SSC-EWI-OR0033
            • SSC-EWI-OR0035
            • SSC-EWI-OR0036
            • SSC-EWI-OR0038
            • SSC-EWI-OR0039
            • SSC-EWI-OR0042
            • SSC-EWI-OR0045
            • SSC-EWI-OR0046
            • SSC-EWI-OR0047
            • SSC-EWI-OR0049
            • SSC-EWI-OR0050
            • SSC-EWI-OR0051
            • SSC-EWI-OR0052
            • SSC-EWI-OR0053
            • SSC-EWI-OR0057
            • SSC-EWI-OR0067
            • SSC-EWI-OR0068
            • SSC-EWI-OR0069
            • SSC-EWI-OR0070
            • SSC-EWI-OR0071
            • SSC-EWI-OR0072
            • SSC-EWI-OR0075
            • SSC-EWI-OR0076
            • SSC-EWI-OR0078
            • SSC-EWI-OR0082
            • SSC-EWI-OR0087
            • SSC-EWI-OR0089
            • SSC-EWI-OR0090
            • SSC-EWI-OR0092
            • SSC-EWI-OR0095
            • SSC-EWI-OR0097
            • SSC-EWI-OR0099
            • SSC-EWI-OR0100
            • SSC-EWI-OR0101
            • SSC-EWI-OR0103
            • SSC-EWI-OR0104
            • SSC-EWI-OR0105
            • SSC-EWI-OR0108
            • SSC-EWI-OR0109
            • SSC-EWI-OR0110
            • SSC-EWI-OR0116
            • SSC-EWI-OR0118
            • SSC-EWI-OR0121
            • SSC-EWI-OR0123
            • SSC-EWI-OR0126
            • SSC-EWI-OR0128
            • SSC-EWI-OR0129
            • SSC-EWI-OR0133
            • SSC-EWI-OR0135
            • SSC-EWI-OR0136
          • Transact-SQL
            • SSC-EWI-TS0001
            • SSC-EWI-TS0009
            • SSC-EWI-TS0010
            • SSC-EWI-TS0013
            • SSC-EWI-TS0016
            • SSC-EWI-TS0017
            • SSC-EWI-TS0023
            • SSC-EWI-TS0024
            • SSC-EWI-TS0025
            • SSC-EWI-TS0026
            • SSC-EWI-TS0032
            • SSC-EWI-TS0034
            • SSC-EWI-TS0035
            • SSC-EWI-TS0036
            • SSC-EWI-TS0037
            • SSC-EWI-TS0039
            • SSC-EWI-TS0041
            • SSC-EWI-TS0043
            • SSC-EWI-TS0044
            • SSC-EWI-TS0045
            • SSC-EWI-TS0046
            • SSC-EWI-TS0047
            • SSC-EWI-TS0049
            • SSC-EWI-TS0055
            • SSC-EWI-TS0056
            • SSC-EWI-TS0057
            • SSC-EWI-TS0060
            • SSC-EWI-TS0061
            • SSC-EWI-TS0063
            • SSC-EWI-TS0067
            • SSC-EWI-TS0070
            • SSC-EWI-TS0072
            • SSC-EWI-TS0073
            • SSC-EWI-TS0074
            • SSC-EWI-TS0075
            • SSC-EWI-TS0076
            • SSC-EWI-TS0077
            • SSC-EWI-TS0078
            • SSC-EWI-TS0079
            • SSC-EWI-TS0080
            • SSC-EWI-TS0081
          • PostgreSQL
            • SSC-EWI-PG0001
            • SSC-EWI-PG0002
            • SSC-EWI-PG0003
            • SSC-EWI-PG0004
            • SSC-EWI-PG0006
            • SSC-EWI-PG0008
            • SSC-EWI-PG0007
            • SSC-EWI-PG0009
            • SSC-EWI-PG0010
            • SSC-EWI-PG0011
            • SSC-EWI-PG0012
            • SSC-EWI-PG0014
            • SSC-EWI-PG0015
            • SSC-EWI-PG0016
          • Redshift
            • SSC-EWI-RS0002
            • SSC-EWI-RS0003
            • SSC-EWI-RS0004
            • SSC-EWI-RS0005
            • SSC-EWI-RS0006
            • SSC-EWI-RS0007
            • SSC-EWI-RS0008
            • SSC-EWI-RS0009
          • Sybase
            • SSC-EWI-SY0001
            • SSC-EWI-SY0002
            • SSC-EWI-SY0003
            • SSC-EWI-SY0004
            • SSC-EWI-SY0005
            • SSC-EWI-SY0006
            • SSC-EWI-SY0007
            • SSC-EWI-SY0008
            • SSC-EWI-SY0009
          • BigQuery
            • SSC-EWI-BQ0001
            • SSC-EWI-BQ0002
            • SSC-EWI-BQ0003
            • SSC-EWI-BQ0004
            • SSC-EWI-BQ0005
            • SSC-EWI-BQ0006
            • SSC-EWI-BQ0007
            • SSC-EWI-BQ0008
            • SSC-EWI-BQ0009
            • SSC-EWI-BQ0011
            • SSC-EWI-BQ0012
            • SSC-EWI-BQ0013
            • SSC-EWI-BQ0014
            • SSC-EWI-BQ0015
          • Greenplum
        • Functional Difference Messages
          • General
            • SSC-FDM-0001
            • SSC-FDM-0002
            • SSC-FDM-0003
            • SSC-FDM-0004
            • SSC-FDM-0005
            • SSC-FDM-0006
            • SSC-FDM-0007
            • SSC-FDM-0008
            • SSC-FDM-0009
            • SSC-FDM-0010
            • SSC-FDM-0011
            • SSC-FDM-0012
            • SSC-FDM-0013
            • SSC-FDM-0014
            • SSC-FDM-0015
            • SSC-FDM-0016
            • SSC-FDM-0017
            • SSC-FDM-0018
            • SSC-FDM-0019
            • SSC-FDM-0020
            • SSC-FDM-0021
            • SSC-FDM-0022
            • SSC-FDM-0023
            • SSC-FDM-0024
            • SSC-FDM-0026
            • SSC-FDM-0027
            • SSC-FDM-0028
            • SSC-FDM-0029
            • SSC-FDM-0030
            • SSC-FDM-0031
            • SSC-FDM-0032
            • SSC-FDM-0033
          • Teradata
            • SSC-FDM-TD0001
            • SSC-FDM-TD0002
            • SSC-FDM-TD0003
            • SSC-FDM-TD0004
            • SSC-FDM-TD0005
            • SSC-FDM-TD0006
            • SSC-FDM-TD0007
            • SSC-FDM-TD0008
            • SSC-FDM-TD0009
            • SSC-FDM-TD0010
            • SSC-FDM-TD0011
            • SSC-FDM-TD0012
            • SSC-FDM-TD0013
            • SSC-FDM-TD0014
            • SSC-FDM-TD0015
            • SSC-FDM-TD0016
            • SSC-FDM-TD0017
            • SSC-FDM-TD0018
            • SSC-FDM-TD0019
            • SSC-FDM-TD0020
            • SSC-FDM-TD0021
            • SSC-FDM-TD0022
            • SSC-FDM-TD0023
            • SSC-FDM-TD0024
            • SSC-FDM-TD0025
            • SSC-FDM-TD0026
            • SSC-FDM-TD0027
            • SSC-FDM-TD0028
            • SSC-FDM-TD0029
            • SSC-FDM-TD0030
            • SSC-FDM-TD0031
            • SSC-FDM-TD0032
            • SSC-FDM-TD0033
          • Oracle
            • SSC-FDM-OR0001
            • SSC-FDM-OR0002
            • SSC-FDM-OR0003
            • SSC-FDM-OR0004
            • SSC-FDM-OR0005
            • SSC-FDM-OR0006
            • SSC-FDM-OR0007
            • SSC-FDM-OR0008
            • SSC-FDM-OR0009
            • SSC-FDM-OR0010
            • SSC-FDM-OR0011
            • SSC-FDM-OR0012
            • SSC-FDM-OR0013
            • SSC-FDM-OR0014
            • SSC-FDM-OR0015
            • SSC-FDM-OR0016
            • SSC-FDM-OR0017
            • SSC-FDM-OR0018
            • SSC-FDM-OR0019
            • SSC-FDM-OR0020
            • SSC-FDM-OR0021
            • SSC-FDM-OR0022
            • SSC-FDM-OR0023
            • SSC-FDM-OR0024
            • SSC-FDM-OR0025
            • SSC-FDM-OR0026
            • SSC-FDM-OR0027
            • SSC-FDM-OR0028
            • SSC-FDM-OR0029
            • SSC-FDM-OR0030
            • SSC-FDM-OR0031
            • SSC-FDM-OR0032
            • SSC-FDM-OR0033
            • SSC-FDM-OR0034
            • SSC-FDM-OR0035
            • SSC-FDM-OR0036
            • SSC-FDM-OR0037
            • SSC-FDM-OR0038
            • SSC-FDM-OR0039
            • SSC-FDM-OR0040
            • SSC-FDM-OR0041
            • SSC-FDM-OR0042
            • SSC-FDM-OR0043
            • SSC-FDM-OR0044
            • SSC-FDM-OR0045
            • SSC-FDM-OR0046
            • SSC-FDM-OR0047
            • SSC-FDM-OR0049
          • Transact-SQL
            • SSC-FDM-TS0001
            • SSC-FDM-TS0002
            • SSC-FDM-TS0003
            • SSC-FDM-TS0004
            • SSC-FDM-TS0005
            • SSC-FDM-TS0006
            • SSC-FDM-TS0007
            • SSC-FDM-TS0008
            • SSC-FDM-TS0009
            • SSC-FDM-TS0010
            • SSC-FDM-TS0011
            • SSC-FDM-TS0012
            • SSC-FDM-TS0013
            • SSC-FDM-TS0014
            • SSC-FDM-TS0015
            • SSC-FDM-TS0016
            • SSC-FDM-TS0017
            • SSC-FDM-TS0018
            • SSC-FDM-TS0019
            • SSC-FDM-TS0020
            • SSC-FDM-TS0021
            • SSC-FDM-TS0022
            • SSC-FDM-TS0023
            • SSC-FDM-TS0024
            • SSC-FDM-TS0025
            • SSC-FDM-TS0026
            • SSC-FDM-TS0027
            • SSC-FDM-TS0028
          • PostgreSQL
            • SSC-FDM-PG0001
            • SSC-FDM-PG0002
            • SSC-FDM-PG0003
            • SSC-FDM-PG0004
            • SSC-FDM-PG0005
            • SSC-FDM-PG0006
            • SSC-FDM-PG0007
            • SSC-FDM-PG0008
            • SSC-FDM-PG0009
            • SSC-FDM-PG0010
            • SSC-FDM-PG0011
            • SSC-FDM-PG0012
            • SSC-FDM-PG0013
            • SSC-FDM-PG0014
          • Redshift
            • SSC-FDM-RS0001
            • SSC-FDM-RS0002
            • SSC-FDM-RS0003
            • SSC-FDM-RS0004
            • SSC-FDM-RS0005
            • SSC-FDM-RS0006
            • SSC-FDM-RS0007
            • SSC-FDM-RS0008
          • Sybase
            • SSC-FDM-SY0001
            • SSC-FDM-SY0002
          • BigQuery
            • SSC-FDM-BQ0001
            • SSC-FDM-BQ0002
            • SSC-FDM-BQ0003
            • SSC-FDM-BQ0004
            • SSC-FDM-BQ0005
            • SSC-FDM-BQ0006
            • SSC-FDM-BQ0007
            • SSC-FDM-BQ0008
            • SSC-FDM-BQ0009
            • SSC-FDM-BQ0010
          • Greenplum
            • SSC-FDM-GP0001
        • Performance Review Messages
          • General
            • SSC-PRF-0001
            • SSC-PRF-0002
            • SSC-PRF-0003
            • SSC-PRF-0004
            • SSC-PRF-0005
            • SSC-PRF-0006
            • SSC-PRF-0007
          • Teradata
          • Oracle
          • Transact-SQL
            • SSC-PRF-TS0001
        • Out-of-Scope
          • SSC-OOS-0001
      • Function References
        • SnowConvert UDFs
        • Oracle
          • BFILENAME_UDF
          • CAST_DATE_UDF
          • CLOSE_BULK_CURSOR_UDF
          • DATE_TO_JULIAN_DAYS_UDF
          • DATE_TO_RR_FORMAT_UDF
          • DATEADD_UDF (DATE, FLOAT)
          • DATEADD_UDF (FLOAT, DATE)
          • DATEADD_UDF (TIMESTAMP, FLOAT)
          • DATEADD_UDF (FLOAT, TIMESTAMP)
          • DATEDIFF_UDF(DATE, DATE)
          • DATEDIFF_UDF(DATE, TIMESTAMP)
          • DATEDIFF_UDF(DATE, INTEGER)
          • DATEDIFF_UDF(TIMESTAMP, TIMESTAMP)
          • DATEDIFF_UDF(TIMESTAMP, DATE)
          • DATEDIFF_UDF(TIMESTAMP, NUMBER)
          • DBMS_OUTPUT.PUT_LINE_UDF
          • DBMS_RANDOM.VALUE_UDF
          • DBMS_RANDOM.VALUE_UDF (DOUBLE, DOUBLE)
          • FETCH_BULK_COLLECTION_RECORDS_UDF (OBJECT, FLOAT, ARRAY)
          • FETCH_BULK_COLLECTION_RECORDS_UDF (OBJECT)
          • FETCH_BULK_COLLECTION_RECORDS_UDF (OBJECT, INTEGER)
          • FETCH_BULK_COLLECTION_RECORDS_UDF (OBJECT, ARRAY)
          • FETCH_BULK_COLLECTIONS_UDF (OBJECT, FLOAT)
          • FETCH_BULK_COLLECTIONS_UDF (OBJECT)
          • FETCH_BULK_RECORD_COLLECTIONS_UDF (OBJECT, FLOAT, ARRAY)
          • FETCH_BULK_RECORD_COLLECTIONS_UDF (OBJECT)
          • FETCH_BULK_RECORD_COLLECTIONS_UDF (OBJECT, INTEGER)
          • FETCH_BULK_RECORD_COLLECTIONS_UDF (OBJECT, ARRAY)
          • INIT_CURSOR_UDF
          • JSON_VALUE_UDF
          • JULIAN_TO_GREGORIAN_DATE_UDF
          • OPEN_BULK_CURSOR_UDF (OBJECT, ARRAY)
          • OPEN_BULK_CURSOR_UDF (OBJECT)
          • REGEXP_LIKE_UDF (STRING, STRING, STRING)
          • REGEXP_LIKE_UDF (STRING, STRING)
          • TIMESTAMP_DIFF_UDF
          • UPDATE_PACKAGE_VARIABLE_STATE_UDF
          • UTL_FILE.FCLOSE_UDF
          • UTL_FILE.FOPEN_UDF (VARCHAR,VARCHAR)
          • UTL_FILE.FOPEN_UDF (VARCHAR, VARCHAR, VARCHAR)
          • UTL_FILE.PUT_LINE_UDF
        • Shared
          • DATEADD_UDF (DATE, STRING)
          • DATEADD_UDF (TIMESTAMP, STRING)
          • DATEADD_UDF (STRING, DATE)
          • DATEADD_UDF (STRING, TIMESTAMP)
          • DATEDIFF_UDF (DATE, STRING)
          • DATEDIFF_UDF (TIMESTAMP, STRING)
          • DATEDIFF_UDF (STRING, DATE)
          • DATEDIFF_UDF (STRING, TIMESTAMP)
          • INTERVAL_ADD_UDF (VARCHAR, VARCHAR, VARCHAR, VARCHAR, CHAR, VARCHAR)
          • INTERVAL_MULTIPLY_UDF (VARCHAR, VARCHAR, INTEGER)
          • INTERVAL_TO_MONTHS_UDF (VARCHAR)
          • INTERVAL_TO_SECONDS_UDF (VARCHAR, VARCHAR)
          • MONTHS_TO_INTERVAL_UDF (VARCHAR, NUMBER)
          • SECONDS_TO_INTERVAL_UDF (VARCHAR, NUMBER)
          • TO_INTERVAL_UDF (TIME)
          • TRUNC_UDF (TIMESTAMP_LTZ, VARCHAR)
          • TRUNC_UDF (NUMBER, NUMBER)
          • TRUNC_UDF (NUMBER)
          • TRUNC_UDF (TIMESTAMP_LTZ)
        • Transact-SQL
          • CAST_NUMERIC_TO_TIMESTAMP_TZ_UDF (NUMBER)
          • CAST_TIME_TO_TIMESTAMP_TZ_UDF (TIME)
          • CAST_TIMESTAMP_TZ_TO_NUMERIC_UDF (TIMESTAMP_TZ)
          • CONSTRAINT_OBJECT_ID_UDF (VARCHAR)
          • DB_ID_UDF(STRING)
          • ERROR_LINE_UDF
          • ERROR_NUMBER_UDF
          • ERROR_PROCEDURE_UDF
          • ERROR_SEVERITY_UDF
          • ERROR_STATE_UDF
          • FORMATMESSAGE_UDF
          • FUNCTION_OBJECT_ID_UDF (VARCHAR)
          • GET_CURRENT_TIMEZONE_UDF
          • IDENTITY_UDF
          • IS_MEMBER_UDF
          • ISDATE_UDF
          • ISNUMERIC_UDF
          • OBJECT_ID_UDF (VARCHAR)
          • OFFSET_FORMATTER (VARCHAR)
          • OPENXML_UDF
          • PARSENAME_UDF
          • PATINDEX_UDF
          • PROCEDURE_OBJECT_ID_UDF (VARCHAR)
          • QUOTENAME_UDF (VARCHAR, VARCHAR)
          • QUOTENAME_UDF (VARCHAR)
          • RAISERROR_UDF (DOUBLE, DOUBLE, DOUBLE, ARRAY)
          • RAISERROR_UDF (VARCHAR, DOUBLE, DOUBLE, ARRAY)
          • ROUND_MILLISECONDS_UDF (TIMESTAMP_TZ)
          • SEQUENCE_OBJECT_ID_UDF (VARCHAR)
          • STR_UDF (FLOAT, VARCHAR)
          • STR_UDF(FLOAT)
          • SUBTRACT_TIMESTAMP_TZ_UDF (TIMESTAMP_TZ, TIMESTAMP_TZ)
          • SUM_TIMESTAMP_TZ_UDF (TIMESTAMP_TZ, TIMESTAMP_TZ)
          • SWITCHOFFSET_UDF (TIMESTAMP_TZ, VARCHAR)
          • TABLE_OBJECT_ID_UDF (VARCHAR)
          • TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(STRING, STRING, ARRAY, ARRAY)
          • FOR_XML_UDF (OBJECT, VARCHAR)
          • FOR_XML_UDF (OBJECT, VARCHAR, VARCHAR)
          • UPDATE_ERROR_VARS_UDF (STRING, STRING, STRING, STRING, STRING, STRING)
          • UPDATE_ERROR_VARS_UDF (STRING, STRING, STRING)
          • VIEW_OBJECT_ID_UDF (VARCHAR)
          • XML_JSON_SIMPLE
        • Teradata
          • CENTURY_UDF
          • CHAR2HEXINT_UDF
          • CHKNUM_UDF
          • COMPUTE_EXPAND_ON_UDF
          • DATE_LONG_UDF
          • DATE_TO_INT_UDF
          • DATEADD_UDF
          • DAY_OF_WEEK_LONG_UDF
          • DAYNAME_LONG_UDF (TIMESTAMP_TZ)
          • DAYNAME_LONG_UDF (TIMESTAMP_TZ, VARCHAR)
          • DAYNUMBER_OF_MONTH_UDF
          • DAYNUMBER_OF_YEAR_UDF
          • DIFF_TIME_PERIOD_UDF
          • EXPAND_ON_UDF
          • EXTRACT_TIMESTAMP_DIFFERENCE_UDF
          • FIRST_DAY_JANUARY_OF_ISO_UDF
          • FIRST_DAY_OF_MONTH_ISO_UDF
          • FULL_MONTH_NAME_UDF
          • GETQUERYBANDVALUE_UDF (VARCHAR, FLOAT, VARCHAR)
          • GETQUERYBANDVALUE_UDF (VARCHAR)
          • INSERT_CURRENCY_UDF
          • INSTR_UDF (STRING, STRING)
          • INSTR_UDF (STRING, STRING, INT)
          • INSTR_UDF (STRING, STRING, DOUBLE, DOUBLE)
          • INT_TO_DATE_UDF
          • INTERVAL_ADD_UDF
          • INTERVAL_MULTIPLY_UDF
          • INTERVAL_DIVIDE_UDF
          • INTERVAL_TO_MONTHS_UDF
          • INTERVAL_TO_SECONDS_UDF
          • ISO_YEAR_PART_UDF
          • JAROWINKLER_UDF
          • JSON_EXTRACT_DOT_NOTATION_UDF
          • JSON_EXTRACT_UDF
          • JULIAN_DAY_UDF
          • JULIAN_TO_DATE_UDF
          • LAST_DAY_DECEMBER_OF_ISO_UDF
          • MONTH_NAME_LONG_UDF
          • MONTH_SHORT_UDF
          • MONTHS_BETWEEN_UDF
          • NULLIFZERO_UDF
          • NVP_UDF
          • PERIOD_UDF
          • PERIOD_INTERSECT_UDF
          • PERIOD_OVERLAPS_UDF
          • PERIOD_TO_TIME_UDF
          • QUARTERNUMBER_OF_YEAR_UDF
          • ROMAN_NUMERALS_MONTH_UDF
          • ROUND_DATE_UDF
          • ROW_COUNT_UDF
          • SECONDS_PAST_MIDNIGHT_UDF
          • SUBSTR_UDF (STRING, FLOAT, FLOAT)
          • SUBSTR_UDF (STRING, FLOAT)
          • TD_DAY_OF_CALENDAR_UDF
          • TD_DAY_OF_WEEK_COMPATIBLE_UDF
          • TD_DAY_OF_WEEK_UDF
          • TD_MONTH_OF_CALENDAR_UDF
          • TD_WEEK_OF_CALENDAR_UDF
          • TD_WEEK_OF_YEAR_UDF
          • TD_YEAR_BEGIN_UDF
          • TD_YEAR_END_UDF
          • TIME_DIFFERENCE_UDF
          • TIMESTAMP_ADD_UDF
          • TIMESTAMP_DIFFERENCE_UDF
          • TO_BYTES_HEX_UDF
          • TRANSLATE_CHK_UDF
          • WEEK_NUMBER_OF_QUARTER_ISO_UDF
          • WEEK_NUMBER_OF_QUARTER_COMPATIBLE_UDF
          • WEEK_NUMBER_OF_QUARTER_UDF
          • WEEK_OF_MONTH_UDF
          • WEEKNUMBER_OF_MONTH_UDF
          • WRAP_NEGATIVE_WITH_ANGLE_BRACKETS_UDF
          • YEAR_BEGIN_ISO_UDF
          • YEAR_END_ISO_UDF
          • YEAR_PART_UDF
          • YEAR_WITH_COMMA_UDF
      • Considerations
        • Teradata
    • Contact Us
    • Others
      • How to Use SnowConvert with Docker
    • Frequently Asked Questions (FAQ)
  • 📘Translation References
    • General
      • Subqueries
    • Teradata
      • Data Migration Considerations
        • UNION ALL Data Migration
      • Session Modes in Teradata
        • TERA Mode For Strings Comparison - NO COLLATE
        • TERA Mode For Strings Comparison - COLLATE
        • ANSI Mode For Strings Comparison - NO COLLATE
        • ANSI Mode For Strings Comparison - COLLATE
      • SQL Translation Reference
        • Data Types
        • DDL
          • Tables
            • WITH DEFAULT
          • Index
          • Views
          • Join Index
          • Schema
        • DML
          • Delete Statement
          • Insert Statement
            • LOGGING ERRORS
          • Select Statement
            • ANY Predicate
            • Expand On Clause
            • Normalize
            • Reset When
            • SAMPLE clause
          • Set Operators
          • Update Statement
          • With Modifier
        • Database DBC
        • Built-in Functions
          • CAST
            • Cast to INTERVAL datatype
            • Cast to DATE using { }
            • Cast from Number Datatypes to Varchar Datatype
          • CURRENT_TIMESTAMP
          • 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
      • SQL to Snowflake Scripting (Procedures)
        • CREATE PROCEDURE
        • CREATE MACRO
        • ACTIVITY_COUNT
        • ABORT and ROLLBACK
        • BEGIN END
        • CASE
        • CURSOR
        • DECLARE
        • DECLARE CONDITION HANDLER
        • DECLARE CONTINUE 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
      • SQL to JavaScript (Procedures)
        • Procedures
        • Macros
        • SnowConvert Procedures Helpers
          • Cursor Helper
          • Exec Helper
          • Functional Equivalence Helpers
          • Into Helper
        • GET DIAGNOSTICS EXCEPTION
      • 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
    • 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
          • Data Type Customization
        • 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
        • CREATE FUNCTION
          • Cursor for a return variable
          • Cursor with IF statement
          • Multiples IFs statement
        • 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
    • Transact-SQL
      • General Language Elements
        • COMPUTED COLUMN
        • EXECUTE
          • System Store Procedures
            • SP_RENAME
            • SP_EXECUTESQL
        • Collate
        • USE
        • OUTER APPLY
      • DDLs
        • Tables
          • Azure Synapse Analytics
          • TEXTIMAGE_ON
        • Index
        • Views
        • Materialized View
        • Procedures
        • FUNCTION
          • SCALAR
          • INLINE TABLE-VALUED
          • MULTI-STATEMENT TABLE-VALUED
      • DMLs
        • Set Operators
        • Between
        • Update
          • UPDATE with LEFT and RIGHT JOIN
        • Select
        • Insert
        • Delete
        • Merge
        • Exists
        • IN
        • Truncate
        • 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
                • CONNECTION
                • DEFAULT
                • ON PARTITION
            • CHECK CONSTRAINT
        • SET
          • ANSI_NULLS
      • 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
          • @@ROWCOUNT
        • XML Functions
          • Value
          • Query
      • Built-in procedures
        • Custom User Defined Procedures
          • SP_ADDEXTENDEDPROPERTY_UDP
      • Snowflake Scripting
        • CREATE PROCEDURE
        • CASE
        • CURSOR
        • DECLARE
        • EXECUTE
        • IF
        • SET
        • CALL
        • WHILE
        • BEGIN and COMMIT Transaction
        • OUTPUT PARAMETERS
        • LABEL and GOTO
        • TRY CATCH
      • System Tables
        • SYS.FOREIGN_KEYS
      • Queries
        • SELECT
        • TOP
    • Sybase IQ
      • SQL Statements
        • CREATE TABLE
          • TEMPORARY TABLES
          • IF NOT EXISTS
          • (ENABLE | DISABLE) RLV STORE
          • IN DBSPACE
          • ON COMMIT
          • AT LOCATION
          • PARTITION BY
          • CONSTRAINTS
          • DEFAULT
        • CREATE VIEW
        • SELECT
      • Built-in functions
      • Data Types
    • Redshift
      • Basic elements
        • Names and identifiers
        • Reserved Keywords
        • Literals
          • NULLS
          • Date, time, and timestamp literals
          • Interval Literals
      • Expressions
        • Compound Expressions
          • Bitwise operators
          • Arithmetic operators
        • Expression lists
      • Conditions
        • Comparison Condition
        • Logical Conditions
        • Pattern-matching conditions
          • LIKE
          • SIMILAR TO
          • POSIX Operators
        • BETWEEN
        • NULL
        • IN
        • EXISTS
      • Data types
        • INTERVAL YEAR TO MONTH Data Type
        • INTERVAL DAY TO SECOND Data Type
        • Numeric Format Models
      • SQL Statements
        • CALL
        • CREATE TABLE AS
          • Table Start
            • LOCAL
            • COLUMNS
            • BACKUP
          • Tabla Attributes
            • DISTSTYLE
            • DISTKEY
            • SORTKEY
        • CREATE TABLE
          • Table Start
            • LOCAL
            • IF NOT EXISTS
            • BACKUP
          • Column Attributes
            • DEFAULT
            • IDENTITY
            • GENERATED BY DEFAULT AS IDENTITY
            • ENCODE
            • DISTKEY
            • SORTKEY
            • COLLATE
          • Column Constraint
            • NOT NULL | NULL
            • UNIQUE | PRIMARY KEY
            • REFERENCES
          • Table Constraints
            • UNIQUE
            • PRIMARY KEY
            • FOREIGN KEY
          • Table Attributes
            • DISTSTYLE
            • DISTKEY
            • SORTKEY
            • ENCODE
        • CREATE EXTERNAL TABLE
        • CREATE VIEW
        • CREATE FUNCTION
        • CREATE MATERIALIZED VIEW
        • CREATE PROCEDURE
          • ARGUMENTS MODE
          • POSITIONAL ARGUMENTS
          • NONATOMIC
          • PROCEDURE BODY
            • BLOCK STATEMENT
            • DECLARE
            • ALIAS DECLARATION
            • VARIABLE DECLARATION
            • LABEL
            • CURSORS
              • DECLARE CURSOR
                • DECLARE REFCURSOR
              • OPEN CURSOR
              • FETCH CURSOR
              • CLOSE CURSOR
            • LOOPS
              • LOOP
              • WHILE
              • FOR
              • EXIT
              • CONTINUE
            • TRANSACTIONS
              • COMMIT
              • ROLLBACK
              • TRUNCATE
            • RAISE
            • EXCEPTION
            • RETURN
            • CONDITIONS
              • IF
              • CASE
          • SECURITY (DEFINER | INVOKER)
        • CREATE SCHEMA
        • CREATE DATABASE
        • DELETE
        • INSERT
        • SELECT
          • WITH clause
          • SELECT list
          • FROM clause
          • WHERE clause
          • CONNECT BY clause
          • GROUP BY clause
          • HAVING clause
          • QUALIFY clause
          • UNION, INTERSECT, and EXCEPT
          • ORDER BY clause
        • SELECT INTO
          • WITH clause
          • SELECT list
          • FROM clause
          • WHERE clause
          • GROUP BY clause
          • HAVING clause
          • UNION, INTERSECT, and EXCEPT
          • ORDER BY clause
          • LIMIT and OFFSET clauses
          • Local Variables and Parameters
        • MERGE
        • UPDATE
        • EXECUTE
      • Built-in functions
        • TO_CHAR
          • For datetime values
        • IDENTITY
      • System catalog tables
    • PostgreSQL & Based Languages
      • Built-in functions
      • Data types
        • Netezza
      • String Comparison
      • DDLs
        • CREATE TABLE
          • Netezza
          • Greenplum
        • CREATE VIEW
        • CREATE MATERIALIZED VIEW
          • Greenplum
  • BigQuery
    • Identifier differences between BigQuery and Snowflake
    • Sql Statements
      • CREATE EXTERNAL TABLE
      • CREATE VIEW
        • View column name list
        • View Options
      • CREATE TABLE
        • COLUMN DEFINITION
      • CREATE TABLE LIKE
      • CREATE TABLE COPY
      • CREATE TABLE SNAPSHOT
      • CREATE TABLE CLONE
    • Data types
      • ANY TYPE
      • ARRAY<T>
      • BYTES
      • GEOGRAPHY
      • INTERVAL
      • JSON
      • STRUCT
      • TIMESTAMP
    • Built-in functions
      • ST_GEOGFROMTEXT
      • FORMAT_DATE
      • ST_GEOGPOINT
    • Operators
  • SQL Engine Release Notes
    • Release Notes
      • 2024
      • 2023
  • SnowConvert AI
    • SnowConvert Migration Assistant
      • Getting Started
      • Troubleshooting
      • Billing
      • Legal Notices
Powered by GitBook
On this page

Last updated 2 months ago

Applies to

Description

Some parts in the output code are omitted for clarity reasons.

A scalar user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a scalar value. ().

These functions are usually used inside the SELECTstatement, or single variable setup (most likely inside a stored procedure).

Transact-SQL Syntax

Snowflake Syntax

Snowflake allows 3 different languages in their user defined functions:

  • SQL

  • JavaScript

  • Java

For now, SnowConvert will support only SQL and JavaScript as target languages.

JavaScript user defined functions allows multiple statements in their bodies, but cannot perform queries to the database. (Scalar JavaScript UDFs Reference)

Sample Source Patterns

Set and Declare Statements

The most common statements in function bodies are the DECLARE and SET statements. For DECLARE statements without default value, the transformation will be ignored. SET statements and DECLARE statements with a default value, will be transformed to a COMMON TABLE EXPRESSION. Each common table expression will contain a column that represents the local variable value.

Transact-SQL

Snowflake

If/Else Statement Transformation

Transact-SQL

Snowflake

Nested Statements

For nested statements, the structured programming is being transformed to a single query. The statements in the control-of-flow are going to be nested in table structures in order to preserve the execution order.

CASE EXPRESSIONS only can return one value per statement

Example

The following code in both programming paradigms is functionally equivalent.

Conditional variables through SELECTs

Variable definition and assignment within conditional statements tends to be somewhat problematic, because references to the variable further down the code would have to know where the variable was last modified. Not only that, but if the reference is within another conditional statement, then there would have to be some kind of redirect that references the previous known assignment to the variable.

In the following scenario, the first IF statement can be transformed without problems, because the contents are straightforward enough. The second and third IF statements are commented out because they're not supported at the moment, since there are statements other than variable assignments through SELECT.

SQL Server

Snowflake

Assign and return a variable

SQL Server

Snowflake

Multiple Function Calls

For this specific pattern there are no obvious queries, but there are multiple calls to multiple functions working on the same variable and returning it at the end. Since Snowflake only supports queries inside its functions, the solution for this block is going to be adding it to a Select and nesting the calls inside, making sure the return value is the same as the one on the source.

SQL Server

Snowflake

Increase a variable based on multiple IF conditions and return its value

For this pattern, a variable is modified (increased in this case) using multiple IF conditions. In the beginning, a set of variables is initialized and used to determine whether the result variable should be increased or not. Finally, the result variable is returned.

SQL Server

Snowflake

Two or more RETURN statements

For this pattern, the IF block containing the return clause that breaks the code flow is added at the end of the body, like the final statement to be executed in a CASE expression.

Basic Case

For this particular scenario, there is no logic between the conditional RETURN statement and the final RETURN statement, so all body will be mapped to a single CASE EXPRESSION.

SQL Server

Snowflake

Common Table Expressions

Common table expressions will be kept as in the original code, and they are going to be concatenated with the generated ones. SnowConvert is able to identify first all the original COMMON TABLE EXPRESSION names in order to avoid generating duplicated names.

SQL Server

Snowflake

Transform to JavaScript UDFs

If there are multiple statements and the function does not access the database in any way, it can be transformed into a JavaScript function keeping the functional equivalence

SQL Server

Snowflake

Known Issues

User-defined functions cannot be used to perform actions that modify the database state

User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target

User-defined functions cannot DECLARE, OPEN, FETCH, CLOSE or DEALLOCATE a CURSOR. Use a Stored Procedure if you need to use cursors.

User-defined functions cannot perform control-of-flow statements such as WHILE if there is at least one call to the database

User-defined functions with references to other user-defined functions that were transformed to Stored Procedures, will be transformed to Stored Procedures too.

For all the unsupported cases, please check the related EWIs and the patterns below to obtain recommendations and possible workarounds.

Conditionals other than if/else statements along side queries

The next scenario involves the use of the "while statement" along side other queries. The problem with this example is that there's no way of transforming the while statement to a CTE inside the WITH clause of the main select, this forces us to transform this statement to JavaScript procedure to maintain the same logic.

SQL Server

Snowflake

Assign a variable using its own value iterating through a rowset

In the following example, the variable @names is used to concatenate multiple values from a column into one single string. The variable is updated on each iteration as shown, which is not supported by SnowFlake UDFs. For this scenario, the function should be transformed into a procedure.

SQL Server

Snowflake query

For the described scenarios above, consider the following limitations:

  1. All the calls to user-defined functions in DML queries such as SELECT, INSERT, DELETE, UPDATE or MERGE will fail because calls to Stored Procedures within these queries are not allowed.

  2. Calls to user-defined functions inside procedures, should be preceeded by the CALL keyword.

Related EWIs

SQL user defined functions only supports one query as their body. They can read from the database, but is not allowed to write or modify it. ().

If/Else statement can be handled in different ways, they can be either transformed to javascript or to SQL using the inside the select allowing conditionals inside the queries, while the javascript transformation is pretty straightforward, the Case statement might not be so obvious at first glance.

This is all aggravated by nesting and complex querying that can be found on input code. That's why a specific is added when these patterns are found.

In this simple pattern, there is a variable declaration, then, that variable is set using a SELECT statement and finally returned. This is going to be migrated to a in order to keep the original behavior.

User-defined functions that use are not supported in SQL and should be transformed to stored procedures in order to keep the functional equivalence.

User-defined functions that have SELECT statements assigning a variable to itself is not supported in Snowflake. See also

Use- defined functions used in will fail during the execution.

: UDF was transformed to Snowflake procedure, calling procedures inside a query is not supported.

: Pending Functional Equivalence Review.

: User defined function was transformed to a Snowflake procedure.

CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'
CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'
IN -> SqlServer_01.sql
CREATE OR ALTER FUNCTION PURCHASING.GetVendorName()
RETURNS NVARCHAR(50) AS
BEGIN
	DECLARE @result NVARCHAR(50)
	DECLARE @BUSINESSENTITYID INT
	
	SET @BUSINESSENTITYID = 1492
	
	SELECT @result = Name FROM PURCHASING.VENDOR WHERE BUSINESSENTITYID = @BUSINESSENTITYID
	
	RETURN @result
END

GO

SELECT PURCHASING.GetVendorName() as vendor_name;
vendor_name            | 
-----------------------+ 
Australia Bike Retailer|
OUT -> SqlServer_01.sql
CREATE OR REPLACE FUNCTION PURCHASING.GetVendorName ()
RETURNS VARCHAR(50)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(
		SELECT
			1492 AS BUSINESSENTITYID
	),
	CTE2 AS
	(
		SELECT
			Name AS RESULT
		FROM
			PURCHASING.VENDOR
		WHERE
			BUSINESSENTITYID = (
				SELECT
					BUSINESSENTITYID
				FROM
					CTE1
			)
	)
	SELECT
		RESULT
	FROM
		CTE2
$$;

SELECT
	PURCHASING.GetVendorName() as vendor_name;
VENDOR_NAME            |
-----------------------+
Australia Bike Retailer|
IN -> SqlServer_02.sql
CREATE OR ALTER FUNCTION PURCHASING.HasActiveFlag(@BusinessEntityID int)
RETURNS VARCHAR(10) AS
BEGIN
	DECLARE @result VARCHAR(10)
	DECLARE @ActiveFlag BIT
	
	SELECT @ActiveFlag = ActiveFlag from PURCHASING.VENDOR v where v.BUSINESSENTITYID = @BusinessEntityID
	
	IF @ActiveFlag = 1
		SET @result = 'YES'
	ELSE IF @ActiveFlag = 0 
		SET @result = 'NO'
	
	RETURN @result
END

GO

SELECT PURCHASING.HasActiveFlag(1516) as has_active_flag;
has_active_flag|
---------------+
NO             |
OUT -> SqlServer_02.sql
CREATE OR REPLACE FUNCTION PURCHASING.HasActiveFlag (P_BUSINESSENTITYID INT)
RETURNS VARCHAR(10)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(

		SELECT
			ActiveFlag AS ACTIVEFLAG
		from
			PURCHASING.VENDOR v
		where
			v.BUSINESSENTITYID = P_BUSINESSENTITYID
	),
	CTE2 AS
	(
		SELECT
			CASE
				WHEN (
					SELECT
						ACTIVEFLAG
					FROM
						CTE1
				) = 1
					THEN 'YES'
				WHEN (
					SELECT
						ACTIVEFLAG
					FROM
						CTE1
				) = 0
					THEN 'NO'
			END AS RESULT
	)
	SELECT
		RESULT
	FROM
		CTE2
$$;

SELECT
	PURCHASING.HasActiveFlag(1516) as has_active_flag;
HAS_ACTIVE_FLAG|
---------------+
NO             |
DECLARE @VendorId AS int;
DECLARE @AccountNumber AS VARCHAR(50);
SELECT @VendorId = poh.VendorID 
    FROM Purchasing.PurchaseOrderHeader poh
    WHERE PurchaseOrderID = 1
SELECT @AccountNumber = v.AccountNumber
    FROM Purchasing.Vendor v
    WHERE v.BusinessEntityID = @VendorId
SELECT V.AccountNumber AccountNumber
FROM (SELECT poh.VendorID VendorId 
         FROM Purchasing.PurchaseOrderHeader poh
         WHERE PurchaseOrderID = 1
) T1, Purchasing.Vendor v
WHERE v.BusinessEntityID = T1.VendorId
AccountNumber|
-------------+
LITWARE0001  |
IN -> SqlServer_03.sql
CREATE or ALTER FUNCTION PURCHASING.SELECTINUDF (
    @param1 varchar(12)
)
RETURNS int
AS
BEGIN
    declare @var1 int;
    declare @var2 int;
    declare @var3 int;

    IF @param1 = 'first'
    BEGIN
        select @var1 = col1 + 10 from table1 WHERE id = 0;
        select @var2 = col1 + 20 from table1 WHERE id = 0;
        select @var3 = col1 + 30 from table1 WHERE id = 0;
    END

    IF @param1 = 'second'
    BEGIN
        declare @var4 int = 10;
        select @var1 = col1 + 40 from table1 WHERE id = 0;
        select @var2 = col1 + 40 from table1 WHERE id = 0;
    END

    IF @param1 = 'third'
    BEGIN
        select col1 from table1 where id = 0;
        select @var1 = col1 + 50 from table1 WHERE id = 0;
        select @var2 = col1 + 50 from table1 WHERE id = 0;
    END

    RETURN @var1
END

SELECT PURCHASING.SELECTINUDF('first') as result; -- Assuming table1.col1 is 0 when ID = 0
RESULT|
------+
    10|
OUT -> SqlServer_03.sql
CREATE OR REPLACE FUNCTION PURCHASING.SELECTINUDF (PARAM1 STRING)
RETURNS INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
    WITH CTE1 AS
    (
        SELECT
            CASE
                WHEN PARAM1 = 'first'
                    THEN (SELECT
                        col1 + 10 AS VAR1 from
                        table1
                        WHERE
                        id = 0)
            END AS VAR1,
            CASE
                WHEN PARAM1 = 'first'
                        THEN (SELECT
                        col1 + 20 AS VAR2 from
                        table1
                        WHERE
                        id = 0)
            END AS VAR2,
            CASE
                WHEN PARAM1 = 'first'
                        THEN (SELECT
                        col1 + 30 AS VAR3 from
                        table1
                        WHERE
                        id = 0)
            END AS VAR3
    ),
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'IF STATEMENT' NODE ***/!!!
    CTE2 AS
    (
        /*    IF @param1 = 'second'
            BEGIN
                declare @var4 int = 10;
                select @var1 = col1 + 40 from table1 WHERE id = 0;
                select @var2 = col1 + 40 from table1 WHERE id = 0;
            END*/
        SELECT
            null
    ),
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'IF STATEMENT' NODE ***/!!!
    CTE3 AS
    (
        /*    IF @param1 = 'third'
            BEGIN
                select col1 from table1 where id = 0;
                select @var1 = col1 + 50 from table1 WHERE id = 0;
                select @var2 = col1 + 50 from table1 WHERE id = 0;
            END*/
        SELECT
            null
    ),
    CTE4 AS
    (

        SELECT
            PURCHASING.SELECTINUDF('first') as result
    )
    SELECT
        VAR1
    FROM
        CTE4
$$ -- Assuming table1.col1 is 0 when ID = 0
;
RESULT|
------+
    10|
IN -> SqlServer_04.sql
CREATE OR ALTER FUNCTION Purchasing.GetTotalFreight()
RETURNS MONEY AS
BEGIN
	DECLARE @Result MONEY
	SELECT @Result = ISNULL(SUM(t.Freight), 0) from Purchasing.PurchaseOrderHeader t
	return @Result
END

GO

select Purchasing.GetTotalFreight() as Result;
Result      |
------------+
1583978.2263|
OUT -> SqlServer_04.sql
CREATE OR REPLACE FUNCTION Purchasing.GetTotalFreight ()
RETURNS NUMBER(38, 4)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(
		SELECT
			NVL(SUM(t.Freight), 0) AS RESULT from
			Purchasing.PurchaseOrderHeader t
	)
	SELECT
		RESULT
	FROM
		CTE1
$$;

select
	Purchasing.GetTotalFreight() as Result;
RESULT      |
------------+
1583978.2263|
IN -> SqlServer_05.sql
CREATE OR ALTER FUNCTION PURCHASING.Foo
(
	@PARAM1	INT
)
RETURNS varchar(25)
AS
BEGIN
	DECLARE @filter INT = @PARAM1
	DECLARE @NAME VARCHAR(25) = (SELECT Name from Purchasing.Vendor v where BusinessEntityID = @filter) 
	SET @NAME = REPLACE(@NAME, 'Australia', 'USA')
	SET @NAME = REPLACE(@NAME, 'Bike', 'Car')
	RETURN @NAME
END

GO

SELECT PURCHASING.Foo(1492) AS Name;
Name            |
----------------+
USA Car Retailer|
OUT -> SqlServer_05.sql
CREATE OR REPLACE FUNCTION PURCHASING.Foo (PARAM1 INT)
RETURNS VARCHAR(25)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(
		SELECT
			PARAM1 AS FILTER
	),
	CTE2 AS
	(
		SELECT
			(SELECT
					Name
				from
					Purchasing.Vendor v
				where
					BusinessEntityID = (
						SELECT
							FILTER
						FROM
							CTE1
					)
			) AS NAME
	),
	CTE3 AS
	(
		SELECT
			REPLACE((
				SELECT
					NAME
				FROM
					CTE3
			), 'Australia', 'USA') AS NAME
	),
	CTE4 AS
	(
		SELECT
			REPLACE((
				SELECT
					NAME
				FROM
					CTE4
			), 'Bike', 'Car') AS NAME
	)
	SELECT
		NAME
	FROM
		CTE4
$$;

SELECT
	PURCHASING.Foo(1492) AS Name;
NAME            |
----------------+
USA Car Retailer|
IN -> SqlServer_06.sql
CREATE OR ALTER FUNCTION PURCHASING.FOO() 
RETURNS MONEY
AS
BEGIN
	declare @firstValue MONEY
	declare @secondValue MONEY
	declare @Result MONEY
	select  @Result = 0
	select 	@firstValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 1
	select 	@secondValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 2
	if @firstValue is not null
		select @Result = @Result + @firstValue
	if @secondValue is not null
		select @Result = @Result + @secondValue
	return @Result 
END

GO

SELECT PURCHASING.Foo() AS Result;
Result  |
--------+
473.1415|
OUT -> SqlServer_06.sql
CREATE OR REPLACE FUNCTION PURCHASING.FOO ()
RETURNS NUMBER(38, 4)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(
		select
			0 AS RESULT
	),
	CTE2 AS
	(
		select
			SubTotal AS FIRSTVALUE
		from
			Purchasing.PurchaseOrderHeader
		where
			PurchaseOrderID = 1
	),
	CTE3 AS
	(
		select
			SubTotal AS SECONDVALUE
		from
			Purchasing.PurchaseOrderHeader
		where
			PurchaseOrderID = 2
	),
	CTE4 AS
	(
		SELECT
			CASE
				WHEN (
					SELECT
						FIRSTVALUE
					FROM
						CTE2
				) is not null
					THEN (
					select
						(
							SELECT
								RESULT
							FROM
								CTE1
						) + (
							SELECT
								FIRSTVALUE
							FROM
								CTE2
						) AS RESULT)
			END AS RESULT
	),
	CTE5 AS
	(
		SELECT
			CASE
				WHEN (
					SELECT
						SECONDVALUE
					FROM
						CTE3
				) is not null
					THEN (
					select
						(
							SELECT
								RESULT
							FROM
								CTE1
						) + (
							SELECT
								SECONDVALUE
							FROM
								CTE3
						) AS RESULT)
				ELSE (SELECT
					RESULT
				FROM
					CTE4)
			END AS RESULT
	)
	SELECT
		RESULT
	FROM
		CTE5
$$;

SELECT
	PURCHASING.Foo() AS Result;
RESULT  |
--------+
473.1415|
IN -> SqlServer_07.sql
CREATE OR ALTER FUNCTION [PURCHASING].[FOO] ()
RETURNS INT
AS
BEGIN
	IF exists (SELECT PreferredVendorStatus FROM Purchasing.Vendor v )
		RETURN 1

	RETURN 0
END

GO

SELECT PURCHASING.FOO() as result;
result|
------+
     1|
OUT -> SqlServer_07.sql
CREATE OR REPLACE FUNCTION PURCHASING.FOO ()
RETURNS INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	SELECT
		CASE
			WHEN exists (SELECT
					PreferredVendorStatus
				FROM
					Purchasing.Vendor v
			)
				THEN 1
			ELSE 0
		END
$$;

SELECT
	PURCHASING.FOO() as result;
RESULT|
------+
     1|
IN -> SqlServer_08.sql
CREATE OR ALTER FUNCTION [PURCHASING].[FOO] 
(
	@status INT
) 
Returns INT
As
Begin 
	Declare @result as int = 0

	;WITH ctetable(RevisionNumber) as 
	(
		SELECT RevisionNumber
		FROM Purchasing.PurchaseOrderHeader poh
		where poh.Status = @status
	),
	finalCte As
	(
		SELECT RevisionNumber FROM ctetable	
	)
	
	Select @result = count(RevisionNumber) from finalCte	
	return @result;
End

GO

SELECT PURCHASING.FOO(4) as result;
result|
------+
  3689|
OUT -> SqlServer_08.sql
CREATE OR REPLACE FUNCTION PURCHASING.FOO (STATUS INT)
Returns INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(
		SELECT
			0 AS RESULT
	),
	ctetable (
		RevisionNumber
	) as
		(
			SELECT
			RevisionNumber
			FROM
			Purchasing.PurchaseOrderHeader poh
			where
			poh.Status = STATUS
		),
		finalCte As
		(
			SELECT
			RevisionNumber
		FROM
			ctetable
		),
		CTE2 AS
		(
		Select
			COUNT(RevisionNumber) AS RESULT from
			finalCte
		)
		SELECT
		RESULT
		FROM
		CTE2
$$;

SELECT
		PURCHASING.FOO(4) as result;
RESULT|
------+
  3689|
IN -> SqlServer_09.sql
CREATE OR ALTER FUNCTION PURCHASING.GetFiscalYear 
(
	@DATE AS DATETIME
)
RETURNS INT
AS
BEGIN
	DECLARE @FiscalYear AS INT
	DECLARE @CurMonth AS INT
	SET @CurMonth = DATEPART(M,@DATE)
	SET @FiscalYear = DATEPART(YYYY, @DATE)
	IF (@CurMonth >= 7)
	BEGIN
		SET @FiscalYear = @FiscalYear + 1 
	END
	RETURN @FiscalYear
END

GO

SELECT PURCHASING.GetFiscalYear('2020-10-10') as DATE;
IN -> SqlServer_10.sql
CREATE OR ALTER FUNCTION PURCHASING.[getCleanChargeCode]
(
	@ChargeCode varchar(50)
)
returns varchar(50) as
begin
	declare @CleanChargeCode varchar(50),@Len int,@Pos int=2
	set @Pos=LEN(@ChargeCode)-1
	while @Pos > 1
	begin
		set @CleanChargeCode=RIGHT(@ChargeCode,@Pos)
		if TRY_CAST(@CleanChargeCode as bigint) is not null
			return @CleanChargeCode
		set @Pos=@Pos-1
	end
	set @Pos=LEN(@ChargeCode)-1
	while @Pos > 1
	begin
		set @CleanChargeCode=LEFT(@ChargeCode,@Pos)
		if TRY_CAST(@CleanChargeCode as bigint) is not null
			return @CleanChargeCode
		set @Pos=@Pos-1
	end
	return null
end

GO

SELECT PURCHASING.[getCleanChargeCode]('16test') AS CleanChargeCode;
DATE|
----+
2021|
CleanChargeCode|
---------------+
16             |
OUT -> SqlServer_09.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.GetFiscalYear (DATE TIMESTAMP_NTZ(3))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		FISCALYEAR INT;
		CURMONTH INT;
	BEGIN
		 
		 
		CURMONTH := DATE_PART(month, :DATE :: TIMESTAMP);
		FISCALYEAR := DATE_PART(year, :DATE :: TIMESTAMP);
		IF ((:CURMONTH >= 7)) THEN
			BEGIN
				FISCALYEAR := :FISCALYEAR + 1;
			END;
		END IF;
		RETURN :FISCALYEAR;
	END;
$$;


SELECT
	!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GetFiscalYear' NODE ***/!!!
	PURCHASING.GetFiscalYear('2020-10-10') as DATE;
OUT -> SqlServer_10.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.getCleanChargeCode (CHARGECODE STRING)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		CLEANCHARGECODE VARCHAR(50);
		LEN INT;
		POS INT := 2;
	BEGIN
		 
		POS := LEN(:CHARGECODE)-1;
		WHILE (:POS > 1) LOOP
			CLEANCHARGECODE := RIGHT(:CHARGECODE, :POS);
			IF (TRY_CAST(:CLEANCHARGECODE as BIGINT) is not null) THEN
				RETURN :CLEANCHARGECODE;
			END IF;
			POS := :POS -1;
		END LOOP;
		POS := LEN(:CHARGECODE)-1;
		WHILE (:POS > 1) LOOP
			CLEANCHARGECODE := LEFT(:CHARGECODE, :POS);
			IF (TRY_CAST(:CLEANCHARGECODE as BIGINT) is not null) THEN
				RETURN :CLEANCHARGECODE;
			END IF;
			POS := :POS -1;
		END LOOP;
		RETURN null;
	END;
$$;

SELECT
	PURCHASING.getCleanChargeCode('16test') !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! AS CleanChargeCode;
DATE  |
------+
2021.0|
CLEANCHARGECODE|
---------------+
16             |
IN -> SqlServer_11.sql
CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS INT
AS
BEGIN
    DECLARE @i int = 0, @p int;
    Select @p = COUNT(*) FROM PURCHASING.VENDOR
    
    WHILE (@p < 1000)
    BEGIN
        SET @i = @i + 1
        SET @p = @p + @i
    END
        
    IF (@i = 6)
        RETURN 1
    
    RETURN @p
END

GO

SELECT PURCHASING.FOO() as result;
result|
------+
  1007|
OUT -> SqlServer_11.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.FOO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        I INT := 0;
        P INT;
    BEGIN
         
        Select
            COUNT(*)
        INTO
            :P
 FROM
            PURCHASING.VENDOR;
        WHILE (:P < 1000) LOOP
            I := :I + 1;
            P := :P + :I;
        END LOOP;
        IF ((:I = 6)) THEN
            RETURN 1;
        END IF;
        RETURN :P;
    END;
$$;

SELECT
    PURCHASING.FOO() !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! as result;
FOO |
----+
1007|
IN -> SqlServer_12.sql
CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @names varchar(8000)
    SET @names = ''
    SELECT @names = ISNULL(@names + ' ', '') + Name from Purchasing.Vendor v 
    return @names              
END

GO

select PURCHASING.FOO() as names;
names                                                                                                                                                                                                                                                          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 Australia Bike Retailer Allenson Cycles Advanced Bicycles Trikes, Inc. Morgan Bike Accessories Cycling Master Chicago Rent-All Greenwood Athletic Company Compete Enterprises, Inc International Light Speed Training Systems Gardner Touring Cycles Internati|
OUT -> SqlServer_12.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.FOO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        NAMES VARCHAR(8000);
    BEGIN
         
        NAMES := '';
        SELECT
            NVL(:NAMES || ' ', 0) + Name
        INTO
            :NAMES
        from
            Purchasing.Vendor v;
        RETURN :NAMES;
    END;
$$;

select
    PURCHASING.FOO() !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! as names;
  1. 📘Translation References
  2. Transact-SQL
  3. DDLs
  4. FUNCTION

SCALAR

Translation reference to convert Transact-SQL UDF (User Defined Functions) with scalar return type to Snowflake.

PreviousFUNCTIONNextINLINE TABLE-VALUED
  • Description
  • Transact-SQL Syntax
  • Snowflake Syntax
  • Sample Source Patterns
  • Set and Declare Statements
  • If/Else Statement Transformation
  • Nested Statements
  • Conditional variables through SELECTs
  • Assign and return a variable
  • Multiple Function Calls
  • Increase a variable based on multiple IF conditions and return its value
  • Two or more RETURN statements
  • Common Table Expressions
  • Transform to JavaScript UDFs
  • Known Issues
  • Conditionals other than if/else statements along side queries
  • Assign a variable using its own value iterating through a rowset
  • Related EWIs
-- Transact-SQL Scalar Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
 [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]
SQL Server Language Reference CREATE FUNCTION subsection
Scalar SQL UDFs Reference
CASE EXPRESSION
EWI
Common Table Expression
@@ROWCOUNT
SELECT @local_variable
COMPUTED COLUMNS
SSC-EWI-0067
SSC-EWI-0073
SSC-FDM-0029