GET DIAGNOSTICS EXCEPTION

Translation reference to convert Teradata GET DIAGNOSTICS EXCEPTION statement to Snowflake Scripting

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

Description

GET DIAGNOSTICS retrieves information about successful, exception, or completion conditions from the Diagnostics Area.

For more information regarding Teradata GET DIAGNOSTICS, check here.

GET DIAGNOSTICS
{
  [ EXCEPTION < condition_number >
    [ < parameter_name | variable_name > = < information_item > ]...
  ] 
  |
  [ < parameter_name | variable_name > = < information_item > ]...
}

Sample Source Patterns

Teradata

Query

IN -> Teradata_01.sql

CREATE PROCEDURE getDiagnosticsSample ()
BEGIN
    DECLARE V_MESSAGE, V_CODE VARCHAR(200);
    DECLARE V_Result INTEGER;
    
    SELECT c1 INTO V_Result FROM tab1;
    GET DIAGNOSTICS EXCEPTION 1
        V_MESSAGE = Message_Text,
        V_CODE = RETURNED_SQLSTATE;
END;

Snowflake Scripting

Query

OUT -> Teradata_01.sql

CREATE OR REPLACE PROCEDURE getDiagnosticsSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "06/18/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        V_MESSAGE VARCHAR(200);
        V_CODE VARCHAR(200);
        V_Result INTEGER;
    BEGIN
         
         
        SELECT
            c1 INTO
            :V_Result
        FROM
            tab1;
            V_MESSAGE := SQLERRM;
            V_CODE := SQLSTATE;
    END;
$$;

Known Issues

CLASS_ORIGIN, CONDITION_NUMBER

The use of GET DIAGNOSTICS for CLASS_ORIGIN, CONDITION_NUMBER is not supported

Teradata

Query

IN -> Teradata_02.sql

CREATE PROCEDURE getDiagnosticsSample ()
BEGIN
    DECLARE V_MESSAGE, V_CODE VARCHAR(200);
    DECLARE V_Result INTEGER;
    
    SELECT c1 INTO V_Result FROM tab1;
    GET DIAGNOSTICS EXCEPTION 5
        V_CLASS = CLASS_ORIGIN,
        V_COND = CONDITION_NUMBER;
END;

Snowflake Scripting

Query

OUT -> Teradata_02.sql

CREATE OR REPLACE PROCEDURE getDiagnosticsSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "06/18/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        V_MESSAGE VARCHAR(200);
        V_CODE VARCHAR(200);
        V_Result INTEGER;
    BEGIN
         
         
        SELECT
            c1 INTO
            :V_Result
        FROM
            tab1;
--            V_CLASS = CLASS_ORIGIN
                                  !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'GET DIAGNOSTICS DETAIL FOR CLASS_ORIGIN' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
             
--            V_COND = CONDITION_NUMBER
                                     !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'GET DIAGNOSTICS DETAIL FOR CONDITION_NUMBER' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
             
    END;
$$;

Related EWIS

1. SSC-EWI-0058: Functionality is not currently supported by Snowflake Scripting.