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
          • Spark SQL - Databricks SQL
        • 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-TS0002
            • SSC-EWI-TS0003
            • 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 IQ
            • 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
          • Spark
            • SSC-EWI-SPK0001
          • Hive
            • SSC-EWI-HV0001
        • 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
            • SSC-FDM-0034
          • 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
            • SSC-FDM-TS0029
            • SSC-FDM-TS0030
            • SSC-FDM-TS0031
          • 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
            • SSC-FDM-PG0015
            • SSC-FDM-PG0016
          • 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 IQ
            • 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-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
      • Built-in functions
    • 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
      • ETL-BI Repointing
        • Power BI Repointing
        • SSIS Repointing
    • 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
    • SparkSQL-DatabricksSQL
      • SQL Statements
        • CREATE TABLE
        • 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
      • PostgreSQL interactive terminal
  • 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
        • Model Preference
      • Troubleshooting
      • Billing
      • Legal Notices
Powered by GitBook
On this page
  • Snowflake SQL syntax
  • Noteworthy details
  • Common Table Expression with SELECT INTO
  • Common Table Expression with other expressions
  • Common Table Expression with Delete From
  • Common Table Expression with MERGE statement
  • Common Table Expression with UPDATE statement
  • Known Issues
  • Related EWIs
  1. Translation References
  2. Transact-SQL
  3. DMLs

Common Table Expression (CTE)

Applies to

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

Common table expressions are supported in Snowflake SQL by default.

Snowflake SQL syntax

Subquery:

[ WITH
       <cte_name1> [ ( <cte_column_list> ) ] AS ( SELECT ...  )
   [ , <cte_name2> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
   [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
]
SELECT ...

Recursive CTE:

[ WITH [ RECURSIVE ]
       <cte_name1> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause )
   [ , <cte_name2> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
   [ , <cte_nameN> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
]
SELECT ...

Where:

anchorClause ::=
    SELECT <anchor_column_list> FROM ...

recursiveClause ::=
    SELECT <recursive_column_list> FROM ... [ JOIN ... ]

Noteworthy details

The RECURSIVE keyword does not exist in T-SQL, and the transformation does not actively add the keyword to the result. A warning is added to the output code in order to state this behavior.

Common Table Expression with SELECT INTO

SQL Server:

IN -> SqlServer_01.sql
WITH ctetable(col1, col2) AS
    (
        SELECT	col1, col2 FROM	t1 poh WHERE poh.col1 = 16 and poh.col2 = 4
    ),
    employeeCte AS
    (
	SELECT BUSINESSENTITYID, VACATIONHOURS FROM employee WHERE BUSINESSENTITYID = (SELECT col1 FROM ctetable)
    ),
    finalCte AS
    (
        SELECT BUSINESSENTITYID, VACATIONHOURS FROM employeeCte  
    ) SELECT * INTO #table2 FROM finalCte;

SELECT * FROM #table2;

Snowflake:

OUT -> SqlServer_01.sql
CREATE OR REPLACE TEMPORARY TABLE T_table2 AS
	WITH ctetable (
		col1,
		col2
	) AS
		   (
		       SELECT
		   		col1,
		   		col2
		       FROM
		   		t1 poh
		       WHERE
		   		poh.col1 = 16 and poh.col2 = 4
		   ),
		   		employeeCte AS
		   		    (
		   			SELECT
		   		BUSINESSENTITYID,
		   		VACATIONHOURS
		       FROM
		   		employee
		       WHERE
		   		BUSINESSENTITYID = (SELECT
		   						col1
		   					FROM
		   						ctetable
		   		)
		   		    ),
		   		finalCte AS
		   		    (
		   		        SELECT
		   		BUSINESSENTITYID,
		   		VACATIONHOURS
		       FROM
		   		employeeCte
		   		    )
		   		SELECT
		       *
		       FROM
		       finalCte;

		       SELECT
		       *
		       FROM
		       T_table2;

Common Table Expression with other expressions

The following transformation occurs when the WITH expression is followed by INSERT or DELETE statements.

SQL Server:

IN -> SqlServer_02.sql
WITH CTE AS( SELECT * from table1)
INSERT INTO Table2 (a,b,c,d)
SELECT a,b,c,d
FROM CTE
WHERE e IS NOT NULL;

Snowflake:

OUT -> SqlServer_02.sql
INSERT INTO Table2 (a, b, c, d)
WITH CTE AS( SELECT
*
from
table1
)
SELECT
a,
b,
c,
d
FROM
CTE AS CTE
WHERE
e IS NOT NULL;

Common Table Expression with Delete From

For this transformation, it will only apply for a CTE (Common Table Expression) with a Delete From, however, only for some specifics CTE. It must have only one CTE, and it must have inside a function of ROW_NUMBER or RANK.

The purpose of the CTE with the Delete must be to remove duplicates from a table. In case that the CTE with Delete intents to remove another kind of data, this transformation will not apply.

Let's see an example. For a working example, we must first create a table with some data.

CREATE TABLE WithQueryTest
(
    ID BIGINT,
    Value BIGINT,
    StringValue NVARCHAR(258)
);

Insert into WithQueryTest values(100, 100, 'First');
Insert into WithQueryTest values(200, 200, 'Second');
Insert into WithQueryTest values(300, 300, 'Third');
Insert into WithQueryTest values(400, 400, 'Fourth');
Insert into WithQueryTest values(100, 100, 'First');

Note that there is a duplicated value. The lines 8 and 12 insert the same value. Now we are going to eliminate the duplicates rows in a table.

WITH Duplicated AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS RN
FROM WithQueryTest
)
DELETE FROM Duplicated
WHERE Duplicated.RN > 1

If we execute a Select from the table, it will show the following result

Note that there are no duplicateds rows. In order to conserve the functionality of these CTE with Delete in Snowflake, it will be transformed to

CREATE OR REPLACE TABLE PUBLIC.WithQueryTest AS SELECT
*
FROM PUBLIC.WithQueryTest
QUALIFY ROW_NUMBER()
OVER (PARTITION BY ID ORDER BY ID) = 1 ;

As you can see, the query is transformed to a Create Or Replace Table.

Let's try it in Snowflake, in order to test it, we need the table too.

CREATE OR REPLACE TABLE PUBLIC.WithQueryTest
(
ID BIGINT,
Value BIGINT,
StringValue VARCHAR(258)
);

Insert into PUBLIC.WithQueryTest values(100, 100, 'First');
Insert into PUBLIC.WithQueryTest values(200, 200, 'Second');
Insert into PUBLIC.WithQueryTest values(300, 300, 'Third');
Insert into PUBLIC.WithQueryTest values(400, 400, 'Fourth');
Insert into PUBLIC.WithQueryTest values(100, 100, 'First');

Now, if we execute the result of the transformation, and then a Select to check if the duplicated rows were deleted, this would be the result.

Common Table Expression with MERGE statement

SQL Server:

IN -> SqlServer_03.sql
WITH ctetable(col1, col2) as 
    (
        SELECT col1, col2
        FROM t1 poh
        where poh.col1 = 16 and poh.col2 = 88
    ),
    finalCte As
    (
        SELECT col1 FROM ctetable  
    )  
    MERGE  
  table1 AS target
  USING finalCte AS source  
  ON (target.ID = source.COL1)
  WHEN MATCHED THEN UPDATE SET target.ID = source.Col1
  WHEN NOT MATCHED THEN INSERT (ID, col1) VALUES (source.COL1, source.COL1 );

Snowflake:

OUT -> SqlServer_03.sql
MERGE INTO table1 AS target
USING (
  --** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
  WITH ctetable (
    col1,
    col2
  ) as
       (
           SELECT
           col1,
           col2
           FROM
           t1 poh
           where
           poh.col1 = 16 and poh.col2 = 88
       ),
           finalCte As
               (
                   SELECT
           col1
           FROM
           ctetable
               )
           SELECT
           *
           FROM
           finalCte
) AS source
ON (target.ID = source.COL1)
WHEN MATCHED THEN
           UPDATE SET
           target.ID = source.Col1
WHEN NOT MATCHED THEN
           INSERT (ID, col1) VALUES (source.COL1, source.COL1);

Common Table Expression with UPDATE statement

SQL Server:

IN -> SqlServer_04.sql
WITH ctetable(col1, col2) AS 
    (
        SELECT col1, col2
        FROM table2 poh
        WHERE poh.col1 = 5 and poh.col2 = 4
    )
UPDATE tab1
SET ID = 8, COL1 = 8
FROM table1 tab1
INNER JOIN ctetable CTE ON tab1.ID = CTE.col1;

Snowflake:

OUT -> SqlServer_04.sql
UPDATE dbo.table1 tab1
    SET
        ID = 8,
        COL1 = 8
    FROM
        (
            WITH ctetable (
                col1,
                col2
            ) AS
                   (
                       SELECT
                           col1,
                           col2
                       FROM
                           table2 poh
                       WHERE
                           poh.col1 = 5 and poh.col2 = 4
                   )
                   SELECT
                       *
                   FROM
                       ctetable
        ) AS CTE
    WHERE
        tab1.ID = CTE.col1;

Known Issues

No issues were found.

Related EWIs

PreviousBulk InsertNextDrops

Last updated 3 months ago

The following transformation occurs when the WITH expression is followed by an SELECT INTO statement and it will be transformed into a .

The following transformation occurs when the WITH expression is followed by MERGE statement and it will be transformed into a .

The following transformation occurs when the WITH expression is followed by an UPDATE statement and it will be transformed into an statement.

: The following subquery matches at least one of the patterns considered invalid and may produce compilation errors.

: Performance warning - recursion for CTE not checked. Might require a recursive keyword.

📘
TEMPORARY TABLE
MERGE INTO
UPDATE
SSC-EWI-0108
SSC-PRF-TS0001