SnowConvert AI – Teradata – SQL in Snowflake Scripting (Prozeduren)¶

ABORT und ROLLBACK¶

Übersetzungsreferenz zur Konvertierung der ABORT- und ROLLBACK-Anweisung von Teradata in Snowflake Scripting

Beschreibung ¶

Die ABORT und ROLLBACK-Anweisungen von Teradata werden in Snowflake Scripting durch eine ROLLBACK-Anweisung ersetzt.

Hier finden Sie mehr Informationen über Teradata ABORT und ROLLBACK.

 ABORT [abort_message] [FROM option] [WHERE abort_condition];

ROLLBACK [WORK] [abort_message] [FROM clause] [WHERE clause];

Beispielhafte Quellcode-Muster ¶

Grundlegendes ABORT und ROLLBACK¶

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE procedureBasicAbort() 
BEGIN
    ABORT;	
    ROLLBACK;
END;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE procedureBasicAbort ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        ROLLBACK;
        ROLLBACK;
    END;
$$;

Bedingtes ABORT und ROLLBACK¶

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE procedureWhereAbort(AnotherValueProc INTEGER) 
BEGIN
    ABORT WHERE AValueProc > 2;
	
    ROLLBACK WHERE (AnotherValueProc > 2);
END;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE procedureWhereAbort (ANOTHERVALUEPROC INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/23/2024" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        IF (AValueProc > 2) THEN
            ROLLBACK;
        END IF;
        IF (:AnotherValueProc > 2) THEN
            ROLLBACK;
        END IF;
    END;
$$;

ABORT und ROLLBACK mit Tabellenreferenzen und FROM-Klausel¶

Teradata ¶

Abfrage¶

 CREATE TABLE  ReferenceTable
    (ColumnValue INTEGER);
  
CREATE TABLE  ReferenceTable2
    (ColumnValue INTEGER);

REPLACE PROCEDURE procedureFromAbort() 
BEGIN
    ROLLBACK FROM ReferenceTable, ReferenceTable2
	WHERE ReferenceTable.ColumnValue = ReferenceTable2.ColumnValue;
    ABORT FROM ReferenceTable, ReferenceTable2
        WHERE ReferenceTable.ColumnValue = ReferenceTable2.ColumnValue;
END;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE TABLE ReferenceTable
(
	ColumnValue INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

CREATE TABLE ReferenceTable2
(
	ColumnValue INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

CREATE OR REPLACE PROCEDURE procedureFromAbort ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		LET _ROW_COUNT FLOAT;
		SELECT
			COUNT(*)
		INTO
			_ROW_COUNT
			FROM
			ReferenceTable,
			ReferenceTable2
				WHERE
			ReferenceTable.ColumnValue = ReferenceTable2.ColumnValue;
			IF (_ROW_COUNT > 0) THEN
			ROLLBACK;
			END IF;
			SELECT
			COUNT(*)
			INTO
			_ROW_COUNT
			FROM
			ReferenceTable,
			ReferenceTable2
			        WHERE
			ReferenceTable.ColumnValue = ReferenceTable2.ColumnValue;
			IF (_ROW_COUNT > 0) THEN
			ROLLBACK;
			END IF;
	END;
$$;

ABORT und ROLLBACK mit Tabellenreferenzen ohne FROM-Klausel¶

Teradata ¶

Abfrage¶

 CREATE TABLE  ReferenceTable
    (ColumnValue INTEGER);
    
REPLACE PROCEDURE procedureFromTableAbort() 
BEGIN
    ROLLBACK WHERE ReferenceTable.ColumnValue > 2;
    ABORT WHERE ReferenceTable.ColumnValue > 4;
END;

Snowflake Scripting ¶

Abort und Rollback¶

 CREATE OR REPLACE TABLE ReferenceTable
(
    ColumnValue INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

CREATE OR REPLACE PROCEDURE procedureFromTableAbort ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET _ROW_COUNT FLOAT;
        SELECT
            COUNT(*)
        INTO
            _ROW_COUNT
        FROM
            ReferenceTable
            WHERE
            ReferenceTable.ColumnValue > 2;
            IF (_ROW_COUNT > 0) THEN
            ROLLBACK;
            END IF;
            SELECT
            COUNT(*)
            INTO
            _ROW_COUNT
            FROM
            ReferenceTable
            WHERE
            ReferenceTable.ColumnValue > 4;
            IF (_ROW_COUNT > 0) THEN
            ROLLBACK;
            END IF;
    END;
$$;

Bekannte Probleme¶

1. Custom Error Message¶

Auch wenn ROLLBACK und ABORT unterstützt werden, ist es nicht möglich, sie mit einer benutzerdefinierten Fehlermeldung zu verwenden.

Teradata ¶

Fehlermeldung¶

 ABORT 'Error message for abort';
ROLLBACK  'Error message for rollback';

Snowflake Scripting ¶

Fehlermeldung¶

 ABORT 'Error message for abort';
ROLLBACK  'Error message for rollback';

2. Aggregate function¶

Die Verwendung der Aggregatfunktion in Kombination mit ABORT/ROLLBACK wird nicht unterstützt

Teradata ¶

Aggregationsfunktion¶

 ROLLBACK WHERE SUM(ATable.AValue) < 2;
ABORT WHERE SUM(ATable.AValue) < 2;

Snowflake Scripting ¶

Aggregationsfunktion¶

 ROLLBACK WHERE SUM(ATable.AValue) < 2;
ABORT WHERE SUM(ATable.AValue) < 2;

ACTIVITY_COUNT¶

Übersetzungsspezifikation für die ACTIVITY_COUNT-Statusvariable

Beschreibung¶

The ACTIVITY_COUNT status variable returns the number of rows affected by an SQL DML statement in an embedded SQL or stored procedure application. For more information, see the Teradata ACTIVITY_COUNT documentation.

Es gibt keine direkte Entsprechung in Snowflake. Es gibt jedoch eine Umgehungsmöglichkeit, um die Verhaltensweise von ACTIVITY_COUNTzu emulieren. Verwenden Sie einfach die folgende Abfrage:

 SELECT $1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Diese Abfrage ruft die erste Spalte des Resultset der zuletzt ausgeführten Abfrage in der aktuellen Sitzung ab und gibt sie zurück. Außerdem kann $1 je nach Art der Abfrage durch „number of rows inserted“, „number of rows updated“ oder „number of rows deleted“ ersetzt werden.

Wie erwartet verhält sich diese Übersetzung nur dann wie ihr Teradata-Pendant, wenn vor dem Aufruf von LAST_QUERY_ID keine anderen Abfragen außer der SQL DML-Anweisung ausgeführt werden.

Beispielhafte Quellcode-Muster¶

Datenkonfiguration¶

Teradata¶

Abfrage¶

 CREATE TABLE employees (
    employee_id INT NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10,2),
    PRIMARY KEY (employee_id)
);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES (1, 'John', 'Doe', 10, 60000.00);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES (2, 'Johny', 'Doey', 10, 65000.00);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES (3, 'Max', 'Smith', 10, 70000.00);

DROP TABLE activity_log;
CREATE TABLE activity_log (
    log_id INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,
    operation VARCHAR(200),
    row_count INT,
    log_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (log_id)
);

Snowflake¶

Abfrage¶

 CREATE OR REPLACE TABLE employees (
    employee_id INT NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10,2),
    PRIMARY KEY (employee_id)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/11/2024" }}'
;

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES (1, 'John', 'Doe', 10, 60000.00);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES (2, 'Johny', 'Doey', 10, 65000.00);

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
VALUES (3, 'Max', 'Smith', 10, 70000.00);

CREATE OR REPLACE TABLE activity_log (
    log_id INT DEFAULT activity_log_log_id.NEXTVAL NOT NULL,
    operation VARCHAR(200),
    row_count INT,
    log_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
    PRIMARY KEY (log_id)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/11/2024" }}'
;

Unterstützte Verwendung¶

Teradata¶

Abfrage¶

 REPLACE PROCEDURE UpdateEmployeeSalaryAndLog ()
BEGIN
    DECLARE row_count1 INT;

    UPDATE employees
    SET salary = 80000
    WHERE department_id = 10;

    -- Get the ACTIVITY_COUNT
    SET row_count1 = ACTIVITY_COUNT;

    -- Insert the ACTIVITY_COUNT into the activity_log table
    INSERT INTO activity_log (operation, row_count)
    VALUES ('UPDATE WHERE dept=10', row_count1);
END;

CALL UpdateEmployeeSalaryAndLog();

SELECT * FROM ACTIVITY_LOG;

Ergebnis¶

LOG_ID | OPERATION    	      | ROW_COUNT | LOG_TIMESTAMP              |
-------+----------------------+-----------+----------------------------+
1      | UPDATE WHERE dept=10 |	3         | 2024-07-10 15:58:46.490000 |

Snowflake¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE UpdateEmployeeSalaryAndLog ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/11/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        row_count1 INT;
    BEGIN
         
        UPDATE employees
    SET salary = 80000
    WHERE department_id = 10;

    -- Get the ACTIVITY_COUNT
        row_count1 := (
    SELECT
        $1
    FROM
        TABLE(RESULT_SCAN(LAST_QUERY_ID()))
        ) /*** SSC-FDM-TD0033 - 'ACTIVITY_COUNT' TRANSFORMATION MIGHT REQUIRE MANUAL ADJUSTMENTS ***/;

        -- Insert the ACTIVITY_COUNT into the activity_log table
        INSERT INTO activity_log (operation, row_count)
        VALUES ('UPDATE WHERE dept=10', :row_count1);
    END;
$$;

CALL UpdateEmployeeSalaryAndLog();

SELECT
    * FROM
    ACTIVITY_LOG;

Ergebnis¶

LOG_ID | OPERATION    	      | ROW_COUNT | LOG_TIMESTAMP            |
-------+----------------------+-----------+--------------------------+
102    | UPDATE WHERE dept=10 |	3         | 2024-07-11T12:42:35.280Z |

Bekannte Probleme¶

If ACTIVITY_COUNT is called twice or more times before executing a DML statement, the transformation might not return the expected values. See SSC-FDM-TD0033.
If ACTIVITY_COUNT is called after a non DML statement was executed, the transformation will not return the expected values. See SSC-FDM-TD0033.
ACTIVITY_COUNT requires manual fixing when inside a SELECT/SET INTO VARIABLE statement and was not able to be identified as a column name. See SSC-EWI-TD0003.

Zugehörige EWIs¶

SSC-FDM-TD0033: ‚ACTIVITY_COUNT‘ TRANSFORMATION MIGHT REQUIRE MANUAL ADJUSTMENTS.

BEGIN END¶

Übersetzungsreferenz zur Konvertierung der BEGIN END-Klausel von Teradata in Snowflake Scripting

BEGIN END TRANSACTION¶

Beschreibung¶

Definiert den Beginn einer expliziten logischen Transaktion im Teradata-Sitzungsmodus.

For more information, see the Teradata BEGIN END Transaction documentation.

 [ BEGIN TRANSACTION | BT ]
     statement
     [ statement ]... ]
[ END TRANSACTION | ET ];

Beispielhaftes Quellcode-Muster ¶

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE BeginEndProcedure()
BEGIN
    DECLARE HELLOSTRING VARCHAR(60);
    BEGIN TRANSACTION
        SET HELLOSTRING = 'HELLO WORLD';
    END TRANSACTION;
END;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE BeginEndProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        HELLOSTRING VARCHAR(60);
    BEGIN
         
        BEGIN TRANSACTION;
        HELLOSTRING := 'HELLO WORLD';
        COMMIT;
    END;
$$;

BEGIN END REQUEST¶

Beschreibung¶

Begrenzt eine SQL-Anfrage mit mehreren Anfragen

For more information, see the Teradata BEGIN END Request documentation.

 BEGIN REQUEST
     statement
     [ statement ]... ]
END REQUEST;

Beispielhaftes Quellcode-Muster ¶

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE BeginEndProcedure()
BEGIN
    DECLARE HELLOSTRING VARCHAR(60);
    BEGIN REQUEST
        SET HELLOSTRING = 'HELLO WORLD';
    END REQUEST;
END;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE BeginEndProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        HELLOSTRING VARCHAR(60);
    BEGIN
         
        BEGIN
            HELLOSTRING := 'HELLO WORLD';
            COMMIT;
        EXCEPTION
            WHEN OTHER THEN
                ROLLBACK;
        END;
    END;
$$;

BEGIN END COMPOUND¶

Beschreibung¶

Begrenzt eine COMPOUND-Anweisung in einer gespeicherten Prozedur.

For more information, see the Teradata BEGIN END Compound documentation.

 label_name: BEGIN
     statement
     [ statement ]... ]
END label_name;

Beispielhaftes Quellcode-Muster ¶

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE BeginEndProcedure()
BEGIN
    DECLARE HELLOSTRING VARCHAR(60);
    label_name: BEGIN
        SET HELLOSTRING = 'HELLO WORLD';
    END label_name;
END;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE BeginEndProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        HELLOSTRING VARCHAR(60);
    BEGIN
         
        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'label_name LABEL' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        label_name:
        BEGIN
            HELLOSTRING := 'HELLO WORLD';
        END;
    END;
$$;

Bekannte Probleme¶

1. Labels not supported in outer BEGIN END blocks¶

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE procedureLabelSingle()
label_name: BEGIN
    DECLARE HELLOSTRING VARCHAR(60);
    SET HELLOSTRING = 'HELLO WORLD';
END label_name;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE procedureLabelSingle ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'label_name LABEL' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
    label_name:
    DECLARE
        HELLOSTRING VARCHAR(60);
    BEGIN
         
        HELLOSTRING := 'HELLO WORLD';
    END;
$$;

Zugehörige EWIs¶

[SSC-EWI-0058](../../general/technical-documentation/issues-and-troubleshooting/conversion-issues/generalEWI. md#ssc-ewi-0058): Die Funktionalität wird derzeit nicht von Snowflake Scripting unterstützt.

CASE¶

Übersetzungsreferenz zur Konvertierung der CASE-Anweisung von Teradata in Snowflake Scripting

Beschreibung ¶

Bietet die bedingte Ausführung von Anweisungen auf der Grundlage der Auswertung des angegebenen bedingten Ausdrucks oder der Gleichheit zweier Operanden.

Die CASE-Anweisung unterscheidet sich von dem SQL CASE-Ausdruck_,\_ der das Ergebnis eines Ausdrucks zurückgibt.

For more information, see the Teradata CASE documentation.

 -- Simple CASE
CASE operant_1
[ WHEN operant_2 THEN
     statement
     [ statement ]... ]...
[ ELSE   
     statement
     [ statement ]... ]
END CASE;

-- Searched CASE
CASE
[ WHEN conditional_expression THEN
     statement
     [ statement ]... ]...
[ ELSE   
     statement
     [ statement ]... ]
END CASE;

Beispielhafte Quellcode-Muster ¶

Sample auxiliary table¶

Teradata¶

 CREATE TABLE case_table(col varchar(30));

Snowflake¶

 CREATE OR REPLACE TABLE case_table (
col varchar(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

Simple Case¶

Teradata¶

Abfrage¶

 CREATE  PROCEDURE caseExample1 ( grade NUMBER )
BEGIN
    CASE grade
        WHEN 10 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Excellent');
        WHEN 9 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Very Good');
        WHEN 8 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Good');
        WHEN 7 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Fair');
        WHEN 6 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Poor');
        ELSE INSERT INTO CASE_TABLE(COL) VALUES ('No such grade');
    END CASE;
END;

CALL caseExample1(6);
CALL caseExample1(4);
CALL caseExample1(10);
SELECT * FROM CASE_TABLE;

Ergebnis¶

|COL          |
|-------------|
|Poor         |
|No such grade|
|Excellent    |

Snowflake Scripting¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE caseExample1 (GRADE NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CASE (grade)
            WHEN 10 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Excellent');
            WHEN 9 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Very Good');
            WHEN 8 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Good');
            WHEN 7 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Fair');
            WHEN 6 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Poor');
            ELSE
                INSERT INTO CASE_TABLE (COL)
                VALUES ('No such grade');
        END CASE;
    END;
$$;

CALL caseExample1(6);
CALL caseExample1(4);
CALL caseExample1(10);
SELECT * FROM CASE_TABLE;

Ergebnis¶

|COL          |
|-------------|
|Poor         |
|No such grade|
|Excellent    |

Searched Case¶

Teradata¶

Abfrage¶

 CREATE PROCEDURE caseExample2 ( grade NUMBER )
BEGIN
    CASE
        WHEN grade = 10 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Excellent');
        WHEN grade = 9 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Very Good');
        WHEN grade = 8 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Good');
        WHEN grade = 7 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Fair');
        WHEN grade = 6 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Poor');
        ELSE INSERT INTO CASE_TABLE(COL) VALUES ('No such grade');
    END CASE;
END;

CALL caseExample2(6);
CALL caseExample2(4);
CALL caseExample2(10);
SELECT * FROM CASE_TABLE;

Ergebnis¶

|COL          |
|-------------|
|Poor         |
|No such grade|
|Excellent    |

Snowflake Scripting¶

Abfrage¶

CREATE OR REPLACE PROCEDURE caseExample2 (GRADE NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CASE
            WHEN :grade = 10 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Excellent');
            WHEN :grade = 9 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Very Good');
            WHEN :grade = 8 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Good');
            WHEN :grade = 7 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Fair');
            WHEN :grade = 6 THEN
                INSERT INTO CASE_TABLE (COL)
                VALUES ('Poor');
                ELSE
                INSERT INTO CASE_TABLE (COL)
                VALUES ('No such grade');
        END CASE;
    END;
$$;

CALL caseExample2(6);

CALL caseExample2(4);

CALL caseExample2(10);

SELECT
    * FROM
    CASE_TABLE;

Ergebnis¶

|COL          |
|-------------|
|Poor         |
|No such grade|
|Excellent    |

Bekannte Probleme¶

Es wurden keine Probleme gefunden.

Zugehörige EWIs¶

Keine zugehörigen EWIs.

CURSOR¶

Übersetzungsreferenz zur Konvertierung der CURSOR-Anweisung von Teradata in Snowflake Scripting

Beschreibung ¶

A cursor is a data structure that is used by stored procedures at runtime to point to a resultset returned by an SQL query. For more information, see the Teradata SQL Cursor Control and DML Statements documentation.

 DECLARE cursor_name [ SCROLL | NO SCROLL ] CURSOR
     [ 
          WITHOUT RETURN
          |
          WITH RETURN [ ONLY ] [ TO [ CALLER | CLIENT ] ]
     ]
     FOR
     cursor_specification [ FOR [ READ ONLY | UPDATE ] ]
     |
     statement_name
;

 FETCH [ [ NEXT | FIRST ] FROM ] cursor_name INTO
    [ variable_name | parameter_name ] [ ,...n ]
;

 OPEN cursor_name
    [ USING [ SQL_identifier | SQL_paramenter ] [ ,...n ] ]
;

 CLOSE cursor_name ;

Beispielhafte Quellcode-Muster ¶

Datenkonfiguration¶

Der folgende Code ist erforderlich, um die Beispielmuster in diesem Abschnitt auszuführen.

Teradata¶

 CREATE TABLE vEmployee(
    PersonID INT,
    LastName VARCHAR(255),
    FirstName VARCHAR(255)
);

CREATE TABLE ResTable(
    Column1 VARCHAR(255)
);

INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (1, 'Smith', 'Christian');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (2, 'Johnson', 'Jhon');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (3, 'Brown', 'William');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (4, 'Williams', 'Gracey');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (5, 'Garcia', 'Julia');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (6, 'Miller', 'Peter');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (7, 'Davis', 'Jannys');

CREATE TABLE TEST_TABLE (
    ColumnA NUMBER, 
    ColumnB VARCHAR(8),
    ColumnC VARCHAR(8));


SELECT * FROM TEST_TABLE;
INSERT INTO TEST_TABLE VALUES (1, '1', '1');
INSERT INTO TEST_TABLE VALUES (2, '2', '2');

Snowflake¶

 CREATE OR REPLACE TABLE vEmployee (
    PersonID INT,
    LastName VARCHAR(255),
    FirstName VARCHAR(255)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

CREATE OR REPLACE TABLE ResTable (
    Column1 VARCHAR(255)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (1, 'Smith', 'Christian');

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (2, 'Johnson', 'Jhon');

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (3, 'Brown', 'William');

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (4, 'Williams', 'Gracey');

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (5, 'Garcia', 'Julia');

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (6, 'Miller', 'Peter');

INSERT INTO vEmployee (PersonID, LastName, FirstName)
VALUES (7, 'Davis', 'Jannys');

CREATE OR REPLACE TABLE TEST_TABLE (
    ColumnA NUMBER(38, 18),
    ColumnB VARCHAR(8),
    ColumnC VARCHAR(8))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

SELECT
    * FROM
    TEST_TABLE;

    INSERT INTO TEST_TABLE
    VALUES (1, '1', '1');

    INSERT INTO TEST_TABLE
    VALUES (2, '2', '2');

Grundlegender Cursor¶

Teradata¶

Cursor-Code¶

 REPLACE PROCEDURE CursorsTest()
BEGIN
    DECLARE val1 VARCHAR(255);
    DECLARE empcursor CURSOR FOR
        SELECT LastName
        FROM vEmployee
        ORDER BY PersonID;
    
    OPEN empcursor;
    FETCH NEXT FROM empcursor INTO val1;
    FETCH NEXT FROM empcursor INTO val1;
    INSERT INTO ResTable(Column1) VALUES (val1);
    CLOSE empcursor;
END;

CALL CursorsTest();
SELECT * FROM ResTable;

Ergebnis¶

Johnson

Snowflake Scripting¶

Cursor-Code¶

 CREATE OR REPLACE PROCEDURE CursorsTest ()
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
        val1 VARCHAR(255);
    BEGIN
         
        LET empcursor CURSOR
        FOR
            SELECT
                LastName
                   FROM
                vEmployee
                   ORDER BY PersonID;
        OPEN empcursor;
        FETCH NEXT FROM empcursor INTO val1;
            FETCH NEXT FROM empcursor INTO val1;
        INSERT INTO ResTable (Column1)
        VALUES (:val1);
            CLOSE empcursor;
    END;
$$;

CALL CursorsTest();

SELECT
    * FROM
    ResTable;

Ergebnis¶

Johnson

Einzelner rückgabefähiger Cursor¶

Die folgende Prozedur soll ein Resultset zurückgeben, da sie in der Kopfzeile die Eigenschaft DYNAMIC RESULT SETS 1 hat, der Cursor die Eigenschaft WITH RETURN hat und im Body geöffnet wird.

Teradata¶

Cursor-Code¶

 REPLACE PROCEDURE spSimple ()
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE result_set CURSOR WITH RETURN ONLY FOR
    SELECT *
    FROM vEmployee;
        
    OPEN result_set;
END;

CALL spSimple();

Ergebnis¶

PersonID|LastName|FirstName|
--------+--------+---------+
       7|Davis   |Jannys   |
       5|Garcia  |Julia    |
       3|Brown   |William  |
       1|Smith   |Christian|
       6|Miller  |Peter    |
       4|Williams|Gracey   |
       2|Johnson |Jhon     |

Snowflake Scripting¶

Cursor-Code¶

 CREATE OR REPLACE PROCEDURE spSimple ()
RETURNS TABLE (
)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET result_set CURSOR FOR
            SELECT * FROM vEmployee;
        OPEN result_set;
        RETURN TABLE(resultset_from_cursor(result_set));
    END;
$$;

CALL spSimple();

Ergebnis¶

PERSONID|LASTNAME|FIRSTNAME|
--------+--------+---------+
       1|Smith   |Christian|
       2|Johnson |Jhon     |
       3|Brown   |William  |
       4|Williams|Gracey   |
       5|Garcia  |Julia    |
       6|Miller  |Peter    |
       7|Davis   |Jannys   |

Mehrere rückgabefähige Cursors¶

Die folgende Prozedur soll mehrere Ergebnisse zurückgeben, wenn die Eigenschaft DYNAMIC RESULT SETS in der Kopfzeile größer als 1 ist, die Prozedur mehrere Cursors mit der Eigenschaft WITH RETURN hat und diese gleichen Cursors im Body geöffnet werden.

Teradata¶

Cursor-Code¶

 REPLACE PROCEDURE spTwoOrMore()
DYNAMIC RESULT SETS 2
BEGIN
    DECLARE result_set CURSOR WITH RETURN ONLY FOR
        SELECT * FROM SampleTable2;

    DECLARE result_set2 CURSOR WITH RETURN ONLY FOR
	SELECT Column11 FROM SampleTable1;
    OPEN result_set2;
    OPEN result_set;
END;

CALL spTwoOrMore();

Ergebnis¶

ColumnA|ColumnB|ColumnC|
-------+-------+-------+
      2|2      |2      |
      1|1      |1      |

PersonID|LastName|FirstName|
--------+--------+---------+
       7|Davis   |Jannys   |
       5|Garcia  |Julia    |
       3|Brown   |William  |
       1|Smith   |Christian|
       6|Miller  |Peter    |
       4|Williams|Gracey   |
       2|Johnson |Jhon     |

Snowflake Scripting¶

Cursor-Code¶

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "SampleTable2", "SampleTable1" **
CREATE OR REPLACE PROCEDURE spTwoOrMore ()
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		tbl_result_set VARCHAR;
		tbl_result_set2 VARCHAR;
		return_arr ARRAY := array_construct();
	BEGIN
		tbl_result_set := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_result_set) AS
			SELECT
				* FROM
				SampleTable2;
		LET result_set CURSOR
		FOR
			SELECT
				*
			FROM
				IDENTIFIER(?);
		tbl_result_set2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_result_set2) AS
			SELECT
				Column11 FROM
				SampleTable1;
		LET result_set2 CURSOR
		FOR
			SELECT
				*
			FROM
				IDENTIFIER(?);
		OPEN result_set2 USING (tbl_result_set2);
		return_arr := array_append(return_arr, :tbl_result_set2);
		OPEN result_set USING (tbl_result_set);
		return_arr := array_append(return_arr, :tbl_result_set);
		--** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
		RETURN return_arr;
	END;
$$;

CALL spTwoOrMore();

Ergebnisse¶

[
  "RESULTSET_B5B0005D_1602_48B7_9EE4_62E1A28B000C",
  "RESULTSET_1371794D_7B77_4DA9_B42E_7981F35CEA9C"
]

ColumnA|ColumnB|ColumnC|
-------+-------+-------+
      2|2      |2      |
      1|1      |1      |

PersonID|LastName|FirstName|
--------+--------+---------+
       7|Davis   |Jannys   |
       5|Garcia  |Julia    |
       3|Brown   |William  |
       1|Smith   |Christian|
       6|Miller  |Peter    |
       4|Williams|Gracey   |
       2|Johnson |Jhon     |

Cursors mit Bindungsvariablen¶

Der folgende Cursor verwendet Bindungsvariablen als Where-Bedingung, um die Abfrage durchzuführen.

Teradata¶

Cursor-Code¶

 REPLACE PROCEDURE TestProcedure (IN param1 NUMBER, param2 VARCHAR(8), param3 VARCHAR(8))
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE cursorExample CURSOR WITH RETURN ONLY FOR
        SELECT * FROM  TEST_TABLE
   	WHERE ColumnA = param1 AND ColumnB LIKE param2 and ColumnC LIKE param3;
    
    OPEN cursorExample;	  
END;

Ergebnis¶

|ColumnA|ColumnB|ColumnC|
+-------+-------+-------+
|      2|2      |2      |

Snowflake Scripting¶

Cursor-Code¶

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "TEST_TABLE" **
CREATE OR REPLACE PROCEDURE TestProcedure (PARAM1 NUMBER(38, 18), PARAM2 VARCHAR(8), PARAM3 VARCHAR(8))
RETURNS TABLE (
)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      LET cursorExample CURSOR
      FOR
         SELECT
            * FROM
            TEST_TABLE
           	WHERE ColumnA = ?
            AND ColumnB ILIKE ?
            and ColumnC ILIKE ?;
      OPEN cursorExample USING (param1, param2, param3);
      RETURN TABLE(resultset_from_cursor(cursorExample));
   END;
$$;

Ergebnis¶

|ColumnA|ColumnB|ColumnC|
+-------+-------+-------+
|      2|2      |2      |

Cursor-FOR-Schleife¶

Es handelt sich um eine Art von Schleife, die einen Cursor verwendet, um Zeilen aus einer SELECT-Anweisung zu holen und dann eine Verarbeitung für jede Zeile durchführt.

Teradata¶

Cursor-Code¶

 REPLACE PROCEDURE TestProcedure ()
DYNAMIC RESULT SETS 1
BEGIN
    FOR fUsgClass AS cUsgClass CURSOR FOR
        SELECT columnA FROM  TEST_TABLE
    DO
        INSERT INTO ResTable(Column1) VALUES (fUsgClass.columnA);
    END FOR;
END;

CALL TestProcedure();
SELECT * FROM ResTable;

Ergebnis¶

|Column1|
+-------+
|      1|
|      2|

Snowflake Scripting¶

Cursor-Code¶

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "TEST_TABLE", "ResTable" **
CREATE OR REPLACE PROCEDURE TestProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        !!!RESOLVE EWI!!! /*** SSC-EWI-0110 - TRANSFORMATION NOT PERFORMED DUE TO MISSING DEPENDENCIES ***/!!!
        temp_fUsgClass_columnA;
    BEGIN
        LET cUsgClass CURSOR
        FOR
            SELECT
                columnA FROM
                TEST_TABLE;
        --** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
        FOR fUsgClass IN cUsgClass DO
            temp_fUsgClass_columnA := fUsgClass.columnA;
            INSERT INTO ResTable (Column1)
            VALUES (:temp_fUsgClass_columnA);
        END FOR;
    END;
$$;

CALL TestProcedure();

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "ResTable" **
SELECT
    * FROM
    ResTable;

Ergebnis¶

|Column1|
+-------+
|      1|
|      2|

Cursorabruf innerhalb einer Schleife¶

So lassen sich Zeilen aus einem Resultset nacheinander abrufen und jede Zeile verarbeiten.

Teradata¶

Cursor-Code¶

 REPLACE PROCEDURE teradata_fetch_inside_loop() 
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE col_name VARCHAR(255);
    DECLARE col_int INTEGER DEFAULT 1;
    DECLARE cursor_var CURSOR FOR SELECT columnA FROM TEST_TABLE;
    WHILE (col_int <> 0) DO		
        FETCH cursor_var INTO col_name;
        INSERT INTO ResTable(Column1) VALUES (cursor_var.columnA);
        SET col_int = 0;
    END WHILE;
END;

CALL teradata_fetch_inside_loop();
SELECT * FROM ResTable;

Ergebnis¶

|Column1|
+-------+
|      2|

Snowflake Scripting¶

Cursor-Code¶

 CREATE OR REPLACE PROCEDURE teradata_fetch_inside_loop ()
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
        col_name VARCHAR(255);
        col_int INTEGER DEFAULT 1;
    BEGIN
         
         
        LET cursor_var CURSOR
        FOR
            SELECT
                columnA FROM
                TEST_TABLE;
                WHILE (:col_int <> 0) LOOP
            --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
                    FETCH cursor_var INTO col_name;
            INSERT INTO ResTable (Column1)
            VALUES (cursor_var.columnA);
            col_int := 0;
                END LOOP;
    END;
$$;

CALL teradata_fetch_inside_loop();

SELECT
    * FROM
    ResTable;

Ergebnis¶

|Column1|
+-------+
|      2|

Bekannte Probleme¶

Die folgenden Parameter sind in Snowflake Scripting nicht anwendbar.

1. Declare¶

[ SCROLL/NO SCROLL ] Snowflake Scripting unterstützt nur FETCH NEXT.

[ READ-ONLY ] Dies ist die Standardeinstellung in Snowflake Scripting.

[ UPDATE ].

2. Fetch¶

[ NEXT ] Dies ist das Standardverhalten in Snowflake Scripting.

[ FIRST ].

Zugehörige EWIs¶

[SSC-FDM-0020](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/generalFDM. md#ssc-fdm-0020): Mehrere Resultsets werden in temporären Tabellen zurückgegeben.
SSC-PRF-0003: Das Abrufen innerhalb einer Schleife wird als komplexes Muster betrachtet; dies kann die Leistung von Snowflake beeinträchtigen.
SSC-PRF-0004: Diese Anweisung hat Verwendungen für Cursor-FOR-Schleife.

DECLARE CONTINUE HANDLER¶

Übersetzungsreferenz zur Konvertierung des DECLARE CONTINUE-Handlers von Teradata in Snowflake Scripting

Beschreibung ¶

Behandeln Sie Abschlussbedingungen und Ausnahmebedingungen, die nicht schwerwiegend genug sind, um den Kontrollfluss zu beeinträchtigen.

For more information, see the Teradata DECLARE CONTINUE handler documentation.

 DECLARE CONTINUE HANDLER FOR
  { 
    { sqlstate_state_spec | condition_name } [,...] |

    { SQLEXCEPTION | SQLWARNING | NOT FOUND } [,...]

  } handler_action_statement ;

Beispielhafte Quellcode-Muster ¶

DECLARE CONTINUE HANDLER¶

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE PURGING_ADD_TABLE
( 
 IN inDatabaseName     	VARCHAR(30), 
 IN inTableName    		VARCHAR(30)
)
BEGIN
 DECLARE vCHAR_SQLSTATE CHAR(5);
 DECLARE vSUCCESS       CHAR(5);

  DECLARE CONTINUE HANDLER FOR SQLSTATE 'T5628'
  BEGIN
     SET vCHAR_SQLSTATE = SQLCODE;
     SET vSUCCESS    = SQLCODE;
  END;

  SELECT 1;
 
END;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE PURGING_ADD_TABLE
(INDATABASENAME VARCHAR(30), INTABLENAME VARCHAR(30)
)
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
  vCHAR_SQLSTATE CHAR(5);
  vSUCCESS       CHAR(5);
 BEGIN
   
   
  BEGIN
   SELECT
    1;
  EXCEPTION
   WHEN statement_error THEN
    LET errcode := :sqlcode
    LET sqlerrmsg := :sqlerrm
    IF (errcode = '904'
    AND contains(sqlerrmsg, 'invalid value')) THEN
     BEGIN
      vCHAR_SQLSTATE := SQLCODE;
      vSUCCESS := SQLCODE;
     END;
    ELSE
     RAISE
    END IF
  END
 END;
$$;

Bekannte Probleme¶

DECLARE CONTINUE HANDLER FOR SQLSTATE¶

Die Unterstützung der Deklaration von Continue-Handlern für einige SQLSTATE Werte wird derzeit von Snowflake Scripting nicht unterstützt.

Teradata ¶

Abfrage¶

 CREATE PROCEDURE declareConditionExample2 ( )
BEGIN
   DECLARE CONTINUE HANDLER FOR SQLSTATE 'UNSUPPORTED'
     BEGIN
       SET vCHAR_SQLSTATE = SQLCODE;
       SET vSUCCESS    = SQLCODE;
    END;
END;

Snowflake Scripting ¶

CREATE OR REPLACE PROCEDURE declareConditionExample2 ( )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      !!!RESOLVE EWI!!! /*** SSC-EWI-TD0004 - NOT SUPPORTED SQL EXCEPTION ON CONTINUE HANDLER ***/!!!
      DECLARE CONTINUE HANDLER FOR SQLSTATE 'UNSUPPORTED'
      BEGIN
         vCHAR_SQLSTATE := SQLCODE;
         vSUCCESS := SQLCODE;
      END;
   END;
$$;

Zugehörige EWIs¶

[SSC-EWI-TD0004](../../general/technical-documentation/issues-and-troubleshooting/conversion-issues/teradataEWI. md#ssc-ewi-td0004): Nicht unterstützte SQL-Ausnahme beim Continue-Handler.

DECLARE CONDITION HANDLER¶

Übersetzungsreferenz zur Konvertierung des DECLARE CONDITION-Handlers von Teradata in Snowflake Scripting

Beschreibung ¶

Weisen Sie einem SQLSTATE-Code einen Namen zu, oder deklarieren Sie eine benutzerdefinierte Bedingung.

For more information, see the Teradata DECLARE CONDITION handler documentation.

 DECLARE condition_name CONDITION
    [ FOR SQLSTATE [ VALUE ] sqlstate_code ] ;

Beispielhafte Quellcode-Muster ¶

DECLARE CONDITION¶

Teradata ¶

Abfrage¶

 CREATE PROCEDURE declareConditionExample ( )
BEGIN
    DECLARE DB_ERROR CONDITION;
    ...
END;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE declareConditionExample ( )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        DB_ERROR EXCEPTION;
    BEGIN
    END;
$$;

Bekannte Probleme¶

DECLARE CONDITION FOR SQLSTATE¶

Die Unterstützung der Angabe von Bedingungen für SQLSTATE-Werte wird derzeit von Snowflake Scripting nicht unterstützt.

Teradata ¶

Abfrage¶

 CREATE PROCEDURE declareConditionExample2 ( )
BEGIN
    DECLARE ERROR_EXISTS CONDITION FOR SQLSTATE VALUE '42000';
END;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE declareConditionExample2 ( )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        ERROR_EXISTS EXCEPTION;
    BEGIN
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'SET EXCEPTION DETAILS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
-- ERROR_EXISTS CONDITION FOR SQLSTATE VALUE '42000';
    END;
$$;

Zugehörige EWIs¶

[SSC-EWI-0058:](../../general/technical-documentation/issues-and-troubleshooting/conversion-issues/generalEWI. md#ssc-ewi-0058) Die Funktionalität wird derzeit von Snowflake Scripting nicht unterstützt.

DECLARE¶

Übersetzungsreferenz zur Konvertierung der DECLARE-Anweisung von Teradata in Snowflake Scripting

Beschreibung ¶

Deklariert eine oder mehrere lokale Variablen.

For more information, see the Teradata DECLARE documentation.

 DECLARE variable_name [, variable_name ]... DATA_TYPE [ DEFAULT default_value]

Beispielhafte Quellcode-Muster ¶

Teradata ¶

Abfrage¶

 CREATE PROCEDURE declareExample ( )
BEGIN
    DECLARE COL_NAME, COL_TYPE VARCHAR(200) DEFAULT '' ;
    DECLARE COL_COUNT, COL_LEN INTEGER;
END;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE declareExample ( )
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
        COL_NAME VARCHAR(200) DEFAULT '';
        COL_TYPE VARCHAR(200) DEFAULT '';
        COL_COUNT INTEGER;
        COL_LEN INTEGER;
    BEGIN
         
         
        RETURN 1;
    END;
$$;

Bekannte Probleme¶

Es wurden keine Probleme gefunden.

Zugehörige EWIs¶

Keine zugehörigen EWIs.

DML- und DDL-Objekte¶

Beschreibung ¶

DML- und DDL-Objekte werden auf die gleiche Weise übersetzt, unabhängig davon, ob sie sich in gespeicherten Prozeduren befinden oder nicht. Weitere Informationen finden Sie unter den folgenden Links.

Übersetzungreferenzen¶

data-types.md: Vergleichen von Teradata-Datentypen und ihren Äquivalenten in Snowflake.
ddl: Untersuchen der Übersetzung der Datendefinitionssprache (Data Definition Language).
dml: Untersuchen der Übersetzung der Datenbearbeitungssprache (Data Manipulation Language).
built-in-functions: Vergleichen von Funktionen, die in der Laufzeitumgebung beider Sprachen enthalten sind.

EXCEPTION HANDLERS¶

Übersetzungsreferenz zur Konvertierung der EXCEPTION HANDLERS-Klausel von Teradata in Snowflake Scripting

Bemerkung

Einige Teile des Ausgabecodes wurden aus Gründen der Übersichtlichkeit weggelassen.

Beschreibung ¶

Die Einzel- und Mehrfachausnahme-Handler von Teradata werden durch die entsprechenden Handler in Snowflake Scripting ersetzt.

For more information, see the Teradata EXCEPTION HANDLERS documentation.

 DECLARE < handler_type > HANDLER
  FOR  < condition_value_list > < handler_action > ;

Beispielhafte Quellcode-Muster ¶

SQLEXCEPTION HANDLER¶

Teradata ¶

Einzelner Handler¶

 CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        INSERT INTO Proc_Error_Table ('procSample', 'Failed SqlException');
    SELECT * FROM Proc_Error_Table;
END;

Mehrere Handler¶

 CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE ConditionByUser1 CONDITION;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        INSERT INTO Proc_Error_Table ('procSample', 'Failed SqlException');
    DECLARE EXIT HANDLER FOR ConditionByUser1
        INSERT INTO Proc_Error_Table ('procSample', 'Failed ConditionByUser1');
    SELECT * FROM Proc_Error_Table;
END;

Snowflake Scripting ¶

Einzelner Handler¶

 CREATE OR REPLACE PROCEDURE handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
         
        SELECT
            * FROM
            Proc_Error_Table;
    EXCEPTION
            WHEN other THEN
            INSERT INTO Proc_Error_Table
            VALUES ('procSample', 'Failed SqlException');
    END;
$$;

Mehrere Handler¶

 CREATE OR REPLACE PROCEDURE handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        ConditionByUser1 EXCEPTION;
    BEGIN
         
         
        SELECT
            * FROM
            Proc_Error_Table;
    EXCEPTION
            WHEN ConditionByUser1 THEN
            INSERT INTO Proc_Error_Table
            VALUES ('procSample', 'Failed ConditionByUser1');
            WHEN other THEN
            INSERT INTO Proc_Error_Table
            VALUES ('procSample', 'Failed SqlException');
    END;
$$;

Benutzerdefinierte Handler¶

Teradata ¶

Abfrage¶

 CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE EXIT HANDLER FOR Custom1, Custom2, Custom3
      BEGIN
        SET Message1 = 'custom1 and custom2 and custom3';
      END;
    SELECT * FROM Proc_Error_Table;
END;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
         
        SELECT
            * FROM
            Proc_Error_Table;
    EXCEPTION
            WHEN Custom1 OR Custom2 OR Custom3 THEN
            BEGIN
                    Message1 := 'custom1 and custom2 and custom3';
            END;
    END;
$$;

Bekannte Probleme¶

CONTINUE-Handler¶

Gefahr

Ein CONTINUE-Handler in Teradata ermöglicht es, die Ausführung nach der Ausführung einer fehlerhaften Anweisung fortzusetzen. Dies wird von den Ausnahmeblöcken in Snowflake Scripting nicht unterstützt. Condition Handler Teradata-Referenzdokumentation

Teradata ¶

Abfrage¶

 CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        INSERT INTO Proc_Error_Table ('spSample4', 'Failed SqlException');
    SELECT * FROM Proc_Error_Table;
END;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-TD0004 - NOT SUPPORTED SQL EXCEPTION ON CONTINUE HANDLER ***/!!!
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        INSERT INTO Proc_Error_Table
        VALUES ('spSample4', 'Failed SqlException');
        SELECT
            * FROM
            Proc_Error_Table;
    END;
$$;

Andere nicht unterstützte Handler¶

Gefahr

Handler für SQLSTATE, SQLWARNING und NOT FOUND werden nicht unterstützt

Teradata ¶

Abfrage¶

 CREATE PROCEDURE handlerSample ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '42002', SQLWARNING, NOT FOUND
        INSERT INTO Proc_Error_Table ('procSample', 'Failed SqlState or SqlWarning or Not Found');
    SELECT * FROM Proc_Error_Table;
END;

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE handlerSample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/04/2024" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'SQLSTATE, SQLWARNING, NOT-FOUND TYPES HANDLER' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        DECLARE EXIT HANDLER FOR SQLSTATE '42002', SQLWARNING, NOT FOUND
--            INSERT INTO Proc_Error_Table ('procSample', 'Failed SqlState or SqlWarning or Not Found');
        SELECT
            * FROM
            Proc_Error_Table;
    END;
$$;

Zugehörige EWIs¶

[SSC-EWI-0058](../../general/technical-documentation/issues-and-troubleshooting/conversion-issues/generalEWI. md#ssc-ewi-0058): Die Funktionalität wird derzeit nicht von Snowflake Scripting unterstützt.
[SSC-EWI-TD0004](../../general/technical-documentation/issues-and-troubleshooting/conversion-issues/teradataEWI. md#ssc-ewi-td0004): Nicht unterstützte SQL-Ausnahme beim Continue-Handler.

EXECUTE/EXEC¶

Übersetzungsreferenz zur Konvertierung der EXECUTE- oder EXEC-Anweisung von Teradata in Snowflake Scripting

Bemerkung

Einige Teile des Ausgabecodes wurden aus Gründen der Übersichtlichkeit weggelassen.

Beschreibung ¶

Die Teradata-Anweisung EXECUTE erlaubt die Ausführung von vorbereiteten dynamischen SQL oder Makros, Exec hingegen erlaubt nur Makros.

Weitere Informationen zu Teradata EXECUTE/EXEC, finden Sie unter Makro-Formular und Dynamisches SQL Formular

 -- EXECUTE macro syntax
{EXECUTE | EXEC } macro_identifier [ (<parameter_definition>[, ...n] ) ] [;]  

<parameter_definition>:= {parameter_name = constant_expression | constant_expresion}


-- EXECUTE prepared dynamic SQL syntax
EXECUTE prepare_indentifier [<using>|<usingDescriptor>]

<using>:= USING < host_variable >[, ...n]
<host_variable>:= [:] host_variable_name [[INDICATOR] :host_indicator_name]
<usingDescriptor>:= USING DESCRIPTOR [:] descript_area

Beispielhafte Quellcode-Muster ¶

Datenkonfiguration¶

Der folgende Code ist erforderlich, um die Beispielmuster in diesem Abschnitt auszuführen.

Teradata¶

 -- Additional Params: -t JavaScript
CREATE TABLE inventory (
    product_name VARCHAR(50),
    price INTEGER
);

CREATE MACRO dummyMacro AS(
  SELECT * FROM INVENTORY;
);

Snowflake¶

 CREATE OR REPLACE TABLE inventory (
  product_name VARCHAR(50),
  price INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

CREATE OR REPLACE PROCEDURE dummyMacro ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
  // SnowConvert AI Helpers Code section is omitted.
  
  INSERT_TEMP(`SELECT
   *
  FROM
   INVENTORY`,[]);
  return tablelist;
$$;

Vorbereitete Anweisung ausführen¶

Teradata¶

Execute¶

 CREATE PROCEDURE InsertProductInInventory(IN productName VARCHAR(50), IN price INTEGER)
BEGIN
    DECLARE dynamicSql CHAR(200);
    SET dynamicSql = 'INSERT INTO INVENTORY VALUES( ?, ?)';
    PREPARE preparedSql FROM dynamicSql;
    EXECUTE preparedSql USING productName, price;
    
END;

CALL InsertProductInInventory('''Chocolate''', 75);
CALL InsertProductInInventory('''Sugar''', 65);
CALL InsertProductInInventory('''Rice''', 100);

Snowflake Scripting ¶

Execute¶

 CREATE OR REPLACE PROCEDURE InsertProductInInventory (PRODUCTNAME VARCHAR(50), PRICE INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        dynamicSql CHAR(200);
    BEGIN
         
        dynamicSql := 'INSERT INTO INVENTORY
VALUES (?, ?)';
        !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PREPARE STATEMENT' NODE ***/!!!
            PREPARE preparedSql FROM dynamicSql;
        !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
        EXECUTE IMMEDIATE dynamicSql;
    END;
$$;

CALL InsertProductInInventory('''Chocolate''', 75);

CALL InsertProductInInventory('''Sugar''', 65);

CALL InsertProductInInventory('''Rice''', 100);

MAKRO-Anweisung ausführen¶

Teradata¶

Execute¶

 EXECUTE dummyMacro;

Ergebnis¶

+---------------+-------+
| product_name  | price |
+---------------+-------+
| 'Chocolate'   | 75    |
+---------------+-------+
| 'Sugar'       | 65    |
+---------------+-------+
| 'Rice'        | 100   |
+---------------+-------+

Snowflake Scripting ¶

Execute¶

 !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE dummyMacro;

Zugehörige EWIs¶

[SSC-EWI-0030:](../../general/technical-documentation/issues-and-troubleshooting/conversion-issues/generalEWI. md#ssc-ewi-0030) Die nachstehende Anweisung enthält dynamische SQL-Verwendungen.
[SSC-EWI-0073](../../general/technical-documentation/issues-and-troubleshooting/conversion-issues/generalEWI. md#ssc-ewi-0073): Überprüfung der Funktionsäquivalenz ausstehend.

EXECUTE IMMEDIATE¶

Übersetzungsreferenz zur Konvertierung der EXECUTE IMMENDIATE-Anweisung von Teradata in Snowflake Scripting

Bemerkung

Einige Teile des Ausgabecodes wurden aus Gründen der Übersichtlichkeit weggelassen.

Beschreibung ¶

Die Teradata-Anweisung EXECUTE IMMEDIATE ermöglicht die Ausführung von dynamischem SQL, das in Variablen oder Zeichenfolgenliteralen enthalten sind.

For more information, see the Teradata EXECUTE IMMEDIATE documentation.

 -- EXECUTE IMMEDIATE syntax
EXECUTE IMMEDIATE <dynamic_statement>

<dynamic_statement> := {string_literal | string_variable}

Beispielhafte Quellcode-Muster ¶

Datenkonfiguration¶

Der folgende Code ist erforderlich, um die Beispielmuster in diesem Abschnitt auszuführen.

Teradata¶

 CREATE TABLE inventory (
    product_name VARCHAR(50),
    price INTEGER
);

Snowflake¶

 CREATE OR REPLACE TABLE inventory (
    product_name VARCHAR(50),
    price INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

Beispiel ausführen ¶

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE InsertProductInInventory(IN productName VARCHAR(50), IN price INTEGER)
BEGIN
	DECLARE insertStatement VARCHAR(100);
	SET insertStatement = 'INSERT INTO INVENTORY VALUES(' || productName || ', ' || price || ')';
    EXECUTE IMMEDIATE insertStatement;
END;

CALL InsertProductInInventory('''Chocolate''', 75);
CALL InsertProductInInventory('''Sugar''', 65);
CALL InsertProductInInventory('''Rice''', 100);

SELECT product_name, price FROM inventory;

Ergebnis¶

+--------------+-------+
| product_name | price |
+--------------+-------+
| Chocolate    | 75    |
+--------------+-------+
| Sugar        | 65    |
+--------------+-------+
| Rice         | 100   |
+--------------+-------+

Snowflake Scripting ¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE InsertProductInInventory (PRODUCTNAME VARCHAR(50), PRICE INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		insertStatement VARCHAR(100);
	BEGIN
		 
		insertStatement := 'INSERT INTO INVENTORY
VALUES (' || productName || ', ' || price || ')';
		!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
		EXECUTE IMMEDIATE insertStatement;
	END;
$$;

CALL InsertProductInInventory('''Chocolate''', 75);

CALL InsertProductInInventory('''Sugar''', 65);

CALL InsertProductInInventory('''Rice''', 100);

SELECT
	product_name,
	price FROM
	inventory;

Ergebnis¶

+--------------+-------+
| PRODUCT_NAME | PRICE |
+--------------+-------+
| Chocolate    | 75    |
+--------------+-------+
| Sugar        | 65    |
+--------------+-------+
| Rice         | 100   |
+--------------+-------+

Ergebnis¶

column1|column2                  |column3|
-------+-------------------------+-------+
      3|Mundo3                   |    3.3|

Zugehörige EWIs¶

[SSC-EWI-0030](../../general/technical-documentation/issues-and-troubleshooting/conversion-issues/generalEWI. md#ssc-ewi-0030): Die nachstehende Anweisung enthält Verwendungen von dynamischen SQL.

FUNCTION OPTIONS OR DATA ACCESS¶

Bemerkung

Einige Teile des Ausgabecodes wurden aus Gründen der Übersichtlichkeit weggelassen.

Bemerkung

Nicht relevante Anweisung.

Warnung

Beachten Sie, dass diese Anweisung aus der Migration entfernt wurde, da sie eine nicht relevante Syntax darstellt. Das bedeutet, dass sie in Snowflake nicht erforderlich ist.

Beschreibung¶

Funktionsoptionen oder Datenzugriffsoptionen sind Anweisungen, die in Funktionen im Deklarationsteil verwendet werden, um bestimmte Eigenschaften festzulegen. Diese können sein:

CONTAINS SQL
SQL SECURITY DEFINER
COLLATION INVOKER
SPECIFIC FUNCTION_NAME

Beispielhafte Quellcode-Muster¶

Funktionsoptionen¶

Beachten Sie, dass in diesem Beispiel die Funktionsoptionen entfernt wurden, da sie in Snowflake nicht erforderlich sind.

Teradata¶

 CREATE FUNCTION sumValues(A INTEGER, B INTEGER)
   RETURNS INTEGER
   LANGUAGE SQL
   CONTAINS SQL
   SQL SECURITY DEFINER
   SPECIFIC sumTwoValues
   COLLATION INVOKER
   INLINE TYPE 1
   RETURN A + B;

Snowflake¶

 CREATE OR REPLACE FUNCTION sumValues (A INTEGER, B INTEGER)
   RETURNS INTEGER
   LANGUAGE SQL
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
   AS
   $$
      A + B
   $$;

Bekannte Probleme ¶

Es wurden keine Probleme gefunden.

Zugehörige EWIs ¶

Keine zugehörigen EWIs.

GET DIAGNOSTICS EXCEPTION¶

Übersetzungsreferenz zur Konvertierung der GET DIAGNOSTICS EXCEPTION-Anweisung von Teradata in Snowflake Scripting

Bemerkung

Einige Teile des Ausgabecodes wurden aus Gründen der Übersichtlichkeit weggelassen.

Beschreibung ¶

GET DIAGNOSTICS ruft Informationen über Erfolgs-, Ausnahme- oder Abschlussbedingungen aus dem Diagnosebereich ab.

For more information, see the Teradata GET DIAGNOSTICS documentation.

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

Beispielhafte Quellcode-Muster ¶

Teradata ¶

Abfrage¶

 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 ¶

Abfrage¶

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

Bekannte Probleme¶

CLASS_ORIGIN, CONDITION_NUMBER¶

Gefahr

Die Verwendung von GET DIAGNOSTICS für CLASS_ORIGIN, CONDITION_NUMBER wird nicht unterstützt

Teradata ¶

Abfrage¶

 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 ¶

Abfrage¶

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

Zugehörige EWIs¶

[SSC-EWI-0058](../../general/technical-documentation/issues-and-troubleshooting/conversion-issues/generalEWI. md#ssc-ewi-0058): Die Funktionalität wird derzeit nicht von Snowflake Scripting unterstützt.

IF¶

Übersetzungsreferenz zur Konvertierung der IF-Anweisung von Teradata in Snowflake Scripting

Bemerkung

Einige Teile des Ausgabecodes wurden aus Gründen der Übersichtlichkeit weggelassen.

Beschreibung ¶

Bietet eine bedingte Ausführung basierend auf dem Wahrheitswert einer Bedingung.

For more information, see the Teradata IF documentation.

 IF conditional_expression THEN
     statement
     [ statement ]... 
[ ELSEIF conditional_expression THEN
     statement
     [ statement ]... ]...
[ ELSE   
     statement
     [ statement ]... ]
END IF;

Beispielhafte Quellcode-Muster ¶

Sample auxiliary table¶

Teradata¶

 CREATE TABLE if_table(col1 varchar(30));

Snowflake¶

 CREATE OR REPLACE TABLE if_table (
col1 varchar(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

Mögliche IF Variationen¶

Teradata ¶

Code 1¶

 CREATE PROCEDURE ifExample1 ( flag NUMBER )
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   END IF;
END;

CALL ifExample1(1);
SELECT * FROM if_table;

Code 2¶

 CREATE PROCEDURE ifExample2 ( flag NUMBER )
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   ELSE
      INSERT INTO if_table(col1) VALUES ('Unexpected input.');
   END IF;
END;

CALL ifExample2(2);
SELECT * FROM if_table;

Code 3¶

 CREATE PROCEDURE ifExample3 ( flag NUMBER )
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   ELSEIF flag = 2 THEN
      INSERT INTO if_table(col1) VALUES ('two');
   ELSEIF flag = 3 THEN
      INSERT INTO if_table(col1) VALUES ('three');
   END IF;
END;

CALL ifExample3(3);
SELECT * FROM if_table;

Code 4¶

 CREATE PROCEDURE ifExample4 ( flag NUMBER )
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   ELSEIF flag = 2 THEN
      INSERT INTO if_table(col1) VALUES ('two');
   ELSEIF flag = 3 THEN
      INSERT INTO if_table(col1) VALUES ('three');
   ELSE
      INSERT INTO if_table(col1) VALUES ('Unexpected input.');  
   END IF;
END;

CALL ifExample4(4);
SELECT * FROM if_table;

Ergebnis 1¶

|COL1|
|----|
|one |

Ergebnis 2¶

|COL1             |
|-----------------|
|Unexpected input.|

Ergebnis 3¶

|COL1 |
|-----|
|three|

Ergebnis 4¶

|COL1             |
|-----------------|
|Unexpected input.|

Snowflake Scripting¶

Abfrage 1¶

 CREATE OR REPLACE PROCEDURE ifExample1 (FLAG NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF (:flag = 1) THEN
         INSERT INTO if_table (col1)
         VALUES ('one');
      END IF;
   END;
$$;

CALL ifExample1(1);

SELECT
   * FROM
   if_table;

Abfrage 2¶

 CREATE OR REPLACE PROCEDURE ifExample2 (FLAG NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF (:flag = 1) THEN
         INSERT INTO if_table (col1)
         VALUES ('one');
      ELSE
         INSERT INTO if_table (col1)
         VALUES ('Unexpected input.');
      END IF;
   END;
$$;

CALL ifExample2(2);

SELECT
   * FROM
   if_table;

Abfrage 3¶

 CREATE OR REPLACE PROCEDURE ifExample3 (FLAG NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF (:flag = 1) THEN
         INSERT INTO if_table (col1)
         VALUES ('one');
      ELSEIF (:flag = 2) THEN
         INSERT INTO if_table (col1)
         VALUES ('two');
      ELSEIF (:flag = 3) THEN
         INSERT INTO if_table (col1)
         VALUES ('three');
      END IF;
   END;
$$;

CALL ifExample3(3);

SELECT
   * FROM
   if_table;

Abfrage 4¶

 CREATE OR REPLACE PROCEDURE ifExample4 (FLAG NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF (:flag = 1) THEN
         INSERT INTO if_table (col1)
         VALUES ('one');
      ELSEIF (:flag = 2) THEN
         INSERT INTO if_table (col1)
         VALUES ('two');
      ELSEIF (:flag = 3) THEN
         INSERT INTO if_table (col1)
         VALUES ('three');
      ELSE
         INSERT INTO if_table (col1)
         VALUES ('Unexpected input.');
      END IF;
   END;
$$;

CALL ifExample4(4);

SELECT
   * FROM
   if_table;

Ergebnis 1¶

|COL1|
|----|
|one |

Ergebnis 2¶

|COL1             |
|-----------------|
|Unexpected input.|

Ergebnis 3¶

|COL1 |
|-----|
|three|

Ergebnis 4¶

|COL1             |
|-----------------|
|Unexpected input.|

Bekannte Probleme ¶

Es wurden keine Probleme gefunden.

Zugehörige EWIs ¶

Keine zugehörigen EWIs.

LOCKING FOR ACCESS¶

Bemerkung

Einige Teile des Ausgabecodes wurden aus Gründen der Übersichtlichkeit weggelassen.

Bemerkung

Nicht relevante Anweisung.

Warnung

Beachten Sie, dass diese Anweisung aus der Migration entfernt wurde, da sie eine nicht relevante Syntax darstellt. Das bedeutet, dass sie in Snowflake nicht erforderlich ist.

Beschreibung¶

Die Funktionalität des Sperrens einer Zeile in Teradata hängt mit dem Zugriff und den Berechtigungen zusammen. Lesen Sie die folgende Dokumentation, um mehr zu erfahren.

Beispielhafte Quellcode-Muster¶

Zeile sperren¶

Beachten Sie, dass in diesem Beispiel die LOCKING ROW FOR ACCESS gelöscht wurde. Das liegt daran, dass Snowflake Zugriffe mit Rollen und Berechtigungen behandelt. Die Anweisung ist nicht erforderlich.

Teradata¶

 REPLACE VIEW SCHEMA2.VIEW1
AS 
LOCKING ROW FOR ACCESS
SELECT * FROM SCHEMA1.TABLE1;

Snowflake¶

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "SCHEMA1.TABLE1" **
CREATE OR REPLACE VIEW SCHEMA2.VIEW1
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
AS
--** SSC-FDM-0001 - VIEWS SELECTING ALL COLUMNS FROM A SINGLE TABLE ARE NOT REQUIRED IN SNOWFLAKE AND MAY IMPACT PERFORMANCE. **
SELECT
* FROM
SCHEMA1.TABLE1;

Bekannte Probleme ¶

Es wurden keine Probleme gefunden.

Zugehörige EWIs¶

[SSC-FDM-0001](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/generalFDM. md#ssc-fdm-0001): Ansichten, die alle Spalten aus einer einzigen Tabelle auswählen, sind in Snowflake nicht erforderlich.
[SSC-FDM-0007](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/generalFDM. md#ssc-fdm-0007): Element mit fehlenden Abhängigkeiten.

LOOP¶

Übersetzungsreferenz zur Konvertierung der LOOP-Anweisung von Teradata in Snowflake Scripting

Bemerkung

Einige Teile des Ausgabecodes wurden aus Gründen der Übersichtlichkeit weggelassen.

Beschreibung ¶

Die LOOP-Anweisung von Teradata wird in die Syntax von Snowflake Scripting LOOP übersetzt.

For more information, see the Teradata LOOP documentation.

 [label_name:] LOOP
    { sql_statement }
END LOOP [label_name];

Beispielhafte Quellcode-Muster ¶

Teradata ¶

Schleife¶

 CREATE PROCEDURE loopProcedure(OUT resultCounter INTEGER)
BEGIN
    DECLARE counter INTEGER DEFAULT 0;
   
    customeLabel: LOOP 
    	SET counter = counter + 1;
	IF counter = 10 THEN
	    LEAVE customeLabel;
	END IF;
    END LOOP customeLabel;
   
    SET resultCounter = counter;
END;

CALL loopProcedure(:?);

Ergebnis¶

 |resultCounter|
|-------------|
|10           |

Snowflake Scripting ¶

Schleife¶

 CREATE OR REPLACE PROCEDURE loopProcedure (RESULTCOUNTER OUT INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		counter INTEGER DEFAULT 0;
	BEGIN
		 
		LOOP
			counter := counter + 1;
			IF (:counter = 10) THEN
				BREAK CUSTOMELABEL;
			END IF;
		END LOOP CUSTOMELABEL;
		resultCounter := counter;
	END;
$$;

CALL loopProcedure(:?);

Ergebnis¶

 |LOOPPROCEDURE|
|-------------|
|10           |

Bekannte Probleme ¶

Es wurden keine Probleme gefunden.

Zugehörige EWIs ¶

Keine zugehörigen EWIs.

OUTPUT PARAMETERS¶

In diesem Artikel geht es um die aktuelle Transformation der Ausgabeparameter und wie deren Funktionalität emuliert wird.

Bemerkung

Einige Teile des Ausgabecodes wurden aus Gründen der Übersichtlichkeit weggelassen.

Beschreibung¶

Ein Ausgabeparameter ist ein Parameter, dessen Wert aus der gespeicherten Prozedur zurück an die aufrufende Anweisung übergeben wird. Snowflake bietet eine direkte Unterstützung von Ausgabeparametern.

Beispielhafte Quellcode-Muster¶

Einzelner Out-Parameter¶

Teradata¶

 
CREATE PROCEDURE demo.proc_with_single_output_parameters(OUT param1 NUMBER)
BEGIN
 SET param1 = 100;
END;

REPLACE PROCEDURE demo.proc_calling_proc_with_single_output_parameters ()
BEGIN
  DECLARE mytestvar NUMBER;
  CALL demo.proc_with_single_output_parameters(mytestvar);
  INSERT INTO demo.TABLE20 VALUES(mytestvar,432);
END;

Snowflake¶

 CREATE OR REPLACE PROCEDURE demo.proc_with_single_output_parameters (PARAM1 OUT NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
 BEGIN
  param1 := 100;
 END;
$$;

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "demo.TABLE20" **
CREATE OR REPLACE PROCEDURE demo.proc_calling_proc_with_single_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
 DECLARE
  mytestvar NUMBER(38, 18);
 BEGIN
   
  CALL demo.proc_with_single_output_parameters(:mytestvar);
  INSERT INTO demo.TABLE20
  VALUES (:mytestvar,432);
 END;
$$;

Mehrere Out-Parameter¶

Teradata¶

 CREATE PROCEDURE demo.proc_with_multiple_output_parameters(OUT param1 NUMBER, INOUT param2 NUMBER)
BEGIN
  SET param1 = param2;
  SET param2 = 32;
END;

CREATE PROCEDURE demo.proc_calling_proc_with_multiple_output_parameters ()
BEGIN
    DECLARE var1  NUMBER;
    DECLARE var2  NUMBER;
    SET var2 = 34;
    CALL demo.proc_with_multiple_output_parameters(var1, var2);
    INSERT INTO demo.TABLE20 VALUES(var1,var2);
END;

Snowflake¶

 CREATE OR REPLACE PROCEDURE demo.proc_with_multiple_output_parameters (PARAM1 OUT NUMBER(38, 18), PARAM2 OUT NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
  BEGIN
    param1 := param2;
    param2 := 32;
  END;
$$;

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "demo.TABLE20" **
CREATE OR REPLACE PROCEDURE demo.proc_calling_proc_with_multiple_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    var1 NUMBER(38, 18);
    var2 NUMBER(38, 18);
  BEGIN
     
     
    var2 := 34;
    CALL demo.proc_with_multiple_output_parameters(:var1, :var2);
    INSERT INTO demo.TABLE20
    VALUES (:var1, :var2);
  END;
$$;

Zugehörige EWIs ¶

Keine zugehörigen EWIs.

PREPARE¶

Übersetzungsspezifikation zur Konvertierung der PREPARE-Anweisung von Teradata in Snowflake Scripting In diesem Abschnitt wird das PREPARE-Muster in Bezug auf eine Cursorlogik näher betrachtet.

Beschreibung ¶

Bereitet die dynamische Anweisung DECLARE CURSOR vor, um die Erstellung verschiedener Resultsets zu ermöglichen. Erlaubt dynamische Parametermarker.

Weitere Informationen finden Sie in der folgenden Dokumentation.

Teradata syntax:

 PREPARE statement_name FROM { 'statement_string' | statement_string_variable } ;

Wobei:

statement_name ist derselbe Bezeichner wie statement_name in einer DECLARE CURSOR -Anweisung.
statement_string ist der SQL Text, der dynamisch ausgeführt werden soll.
statement_string_variable ist der Name einer lokalen SQL-Variablen oder eines SQL-Parameters oder einer Zeichenfolgen-Variablen, die die SQL-Textzeichenfolge enthält, die dynamisch ausgeführt werden soll.

Bemerkung

Wichtige Informationen

Bei dieser Transformation werden die Cursors umbenannt, da sie nicht dynamisch aktualisiert werden können.

Beispielhafte Quellcode-Muster ¶

Dateneinstellung für Beispiele¶

Für dieses Beispiel verwenden Sie bitte die folgenden ergänzenden Abfragen für den Fall, dass Sie jeden Fall ausführen möchten.

Teradata¶

 CREATE TABLE MyTemporaryTable(
    Col1  INTEGER
);

INSERT INTO MyTemporaryTable(col1) VALUES (1);
SELECT * FROM databaseTest.MyTemporaryTable;


CREATE TABLE MyStatusTable (
    Col1  VARCHAR(2)
);
SELECT * FROM MyStatusTable;

Snowflake¶

 CREATE TABLE MyTemporaryTable (
    Col1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

INSERT INTO MyTemporaryTable (col1) VALUES (1);

SELECT * FROM MyTemporaryTable;

    CREATE TABLE MyStatusTable (
    Col1 VARCHAR(2)
   )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

SELECT * FROM MyStatusTable;

Einfaches Szenario¶

Dieses Beispiel zeigt die Funktionalität für den Fall, dass ein einzelner Cursor nur einmal verwendet wird.

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE simple_scenario()
BEGIN
    --Variables for the example's procedure_results
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT * FROM MyTemporaryTable';
    DECLARE procedure_result INTEGER DEFAULT 0;

    -- Actual Cursor usage
    DECLARE C1 CURSOR FOR S1;
    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
    FETCH C1 INTO procedure_result;
    INSERT INTO databaseTest.MyStatusTable(Col1) VALUES (procedure_result);
    CLOSE C1;
END;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;

Ausgabe¶


Col1
1

Snowflake Scripting ¶

Bemerkung

Verwendungen für Cursors müssen umbenannt und neu deklariert werden.

Abfrage¶

 CREATE OR REPLACE PROCEDURE simple_scenario ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ ""origin"": ""sf_sc"", ""name"": ""snowconvert"", ""version"": {  ""major"": 0,  ""minor"": 0,  ""patch"": ""0"" }, ""attributes"": {  ""component"": ""none"",  ""convertedOn"": ""01/01/0001"" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    --Variables for the example's procedure_results
    SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   * FROM
   MyTemporaryTable';
    procedure_result INTEGER DEFAULT 0;
    S1 RESULTSET;
    prepareQuery_aux_sql VARCHAR;
  BEGIN
     
    -- Actual Cursor usage
     
    prepareQuery_aux_sql := SQL_string_sel;
    S1 := (
      EXECUTE IMMEDIATE prepareQuery_aux_sql
    );
    LET CURSOR_S1_INSTANCE_V0 CURSOR
    FOR
      S1;
    OPEN CURSOR_S1_INSTANCE_V0;
    FETCH
      CURSOR_S1_INSTANCE_V0
    INTO
      procedure_result;
    INSERT INTO databaseTest.MyStatusTable (Col1)
    VALUES (procedure_result);
    CLOSE CURSOR_S1_INSTANCE_V0;
  END;
$$;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;

Ausgabe¶


Col1
1

Einfaches Szenario mit RETURN ONLY¶

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE simple_scenario()
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT * FROM MyTemporaryTable';
    DECLARE procedure_result VARCHAR(100);
    DECLARE C1 CURSOR WITH RETURN ONLY FOR S1;

    SET procedure_result = '';
    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
END;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;

Ausgabe¶


Col1
1

Snowflake Scripting ¶

Bemerkung

Verwendungen für Cursors müssen umbenannt und neu deklariert werden.

Abfrage¶

 CREATE OR REPLACE PROCEDURE simple_scenario ()
RETURNS TABLE (
)
LANGUAGE SQL
COMMENT = '{ ""origin"": ""sf_sc"", ""name"": ""snowconvert"", ""version"": {  ""major"": 0,  ""minor"": 0,  ""patch"": ""0"" }, ""attributes"": {  ""component"": ""none"",  ""convertedOn"": ""01/01/0001"" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   * FROM
   MyTemporaryTable';
    procedure_result VARCHAR(100);
    S1 RESULTSET;
    prepareQuery_aux_sql VARCHAR;
  BEGIN
     
     
    procedure_result := '';
    prepareQuery_aux_sql := SQL_string_sel;
    S1 := (
      EXECUTE IMMEDIATE prepareQuery_aux_sql
    );
    LET CURSOR_S1_INSTANCE_V0 CURSOR
    FOR
      S1;
    OPEN CURSOR_S1_INSTANCE_V0;
    RETURN TABLE(resultset_from_cursor(CURSOR_S1_INSTANCE_V0));
  END;
$$;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;

Ausgabe¶


Col1
1

Wiederverwendeter Cursor-Case¶

Teradata ¶

Abfrage¶

 CREATE PROCEDURE fetch_simple_reused_cursor(OUT procedure_result INTEGER)
BEGIN
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT col1 FROM MyTemporaryTable WHERE col1 = 1';

    DECLARE C1 CURSOR FOR S1;
    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
    FETCH C1 INTO procedure_result;
    CLOSE C1;

    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
    FETCH C1 INTO procedure_result;
    CLOSE C1;
END;

Ausgabe¶

No returning information.

Snowflake Scripting ¶

Bemerkung

Verwendungen für Cursors müssen umbenannt und neu deklariert werden.

Abfrage¶

 CREATE OR REPLACE PROCEDURE fetch_simple_reused_cursor (
--                                                        OUT
                                                            PROCEDURE_RESULT INTEGER)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   col1 FROM
   MyTemporaryTable
WHERE col1 = 1';
        S1 RESULTSET;
        prepareQuery_aux_sql VARCHAR;
    BEGIN
         
        prepareQuery_aux_sql := SQL_string_sel;
        S1 := (
            EXECUTE IMMEDIATE prepareQuery_aux_sql
        );
        LET CURSOR_S1_INSTANCE_V0 CURSOR
        FOR
            S1;
        OPEN CURSOR_S1_INSTANCE_V0;
            FETCH
            CURSOR_S1_INSTANCE_V0
        INTO procedure_result;
            CLOSE CURSOR_S1_INSTANCE_V0;
        prepareQuery_aux_sql := SQL_string_sel;
        S1 := (
            EXECUTE IMMEDIATE prepareQuery_aux_sql
        );
        LET CURSOR_S1_INSTANCE_V1 CURSOR
        FOR
            S1;
        OPEN CURSOR_S1_INSTANCE_V1;
            FETCH
            CURSOR_S1_INSTANCE_V1
        INTO procedure_result;
            CLOSE CURSOR_S1_INSTANCE_V1;
        RETURN procedure_result;
    END;
$$;

Ausgabe¶

No returning information.

Geänderte Abfrage vor der Verwendung¶

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE fetch_modified_query_cursor()
BEGIN
    --Variables for the example's procedure_results
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT col1 FROM MyTemporaryTable WHERE col1 = 1';
    DECLARE procedure_result INTEGER DEFAULT 0;
    -- Actual Cursor usages
    DECLARE C1 CURSOR FOR S1;
    PREPARE S1 FROM SQL_string_sel;

    -- This modification does not take effect since S1 is already staged for the Cursor
    SET SQL_string_sel = 'SELECT col1 FROM MyTemporaryTable WHERE col1 = 0';
    OPEN C1;
    FETCH C1 INTO procedure_result;
    INSERT INTO databaseTest.MyStatusTable(Col1) VALUES (procedure_result);
    CLOSE C1;
END;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;

Ausgabe¶


Col1
1

Snowflake Scripting ¶

Bemerkung

Verwendungen für Cursors müssen umbenannt und neu deklariert werden.

Abfrage¶

 CREATE OR REPLACE PROCEDURE fetch_modified_query_cursor ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ ""origin"": ""sf_sc"", ""name"": ""snowconvert"", ""version"": {  ""major"": 0,  ""minor"": 0,  ""patch"": ""0"" }, ""attributes"": {  ""component"": ""none"",  ""convertedOn"": ""01/01/0001"" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    --Variables for the example's procedure_results
    SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   col1 FROM
   MyTemporaryTable
WHERE col1 = 1';
    procedure_result INTEGER DEFAULT 0;
    S1 RESULTSET;
    prepareQuery_aux_sql VARCHAR;
  BEGIN
     
    -- Actual Cursor usages
     
    prepareQuery_aux_sql := SQL_string_sel;
    S1 := (
      EXECUTE IMMEDIATE prepareQuery_aux_sql
    );
    LET CURSOR_S1_INSTANCE_V0 CURSOR
    FOR
      S1;
    -- This modification does not take effect since S1 is already staged for the Cursor
    SQL_string_sel := 'SELECT
   col1 FROM
   MyTemporaryTable
WHERE col1 = 0';
    OPEN CURSOR_S1_INSTANCE_V0;
    FETCH
      CURSOR_S1_INSTANCE_V0
    INTO
      procedure_result;
    INSERT INTO databaseTest.MyStatusTable (Col1)
    VALUES (procedure_result);
    CLOSE CURSOR_S1_INSTANCE_V0;
  END;
$$;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;

Ausgabe¶


Col1
1

Einfacher Cursor kombiniert mit keinem PREPARE-Muster¶

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE fetch_cursor_ignored_query_cursor()
BEGIN
    --Variables for the example's procedure_results
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT * FROM MyTemporaryTable WHERE col1 = 1';
    DECLARE intermediate_result INTEGER;
    DECLARE procedure_result INTEGER DEFAULT 0;
    DECLARE C2 CURSOR FOR SELECT col1 FROM MyTemporaryTable WHERE col1 = 1;

    -- Actual Cursor usage
    DECLARE C1 CURSOR FOR S1;
    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
    FETCH C1 INTO intermediate_result;
    CLOSE C1;
    SET procedure_result = intermediate_result;
    INSERT INTO databaseTest.MyStatusTable(Col1) VALUES (procedure_result);

    OPEN C2;
    FETCH C2 INTO intermediate_result;
    CLOSE C2;
    SET procedure_result = procedure_result + intermediate_result;
END;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;

Ausgabe¶


Col1
1

Snowflake Scripting ¶

Bemerkung

Verwendungen für Cursors müssen umbenannt und neu deklariert werden.

Abfrage¶

 CREATE OR REPLACE PROCEDURE fetch_cursor_ignored_query_cursor ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ ""origin"": ""sf_sc"", ""name"": ""snowconvert"", ""version"": {  ""major"": 0,  ""minor"": 0,  ""patch"": ""0"" }, ""attributes"": {  ""component"": ""none"",  ""convertedOn"": ""01/01/0001"" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    --Variables for the example's procedure_results
    SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   * FROM
   MyTemporaryTable
WHERE col1 = 1';
    intermediate_result INTEGER;
    procedure_result INTEGER DEFAULT 0;
    S1 RESULTSET;
    prepareQuery_aux_sql VARCHAR;
  BEGIN
     
    -- Actual Cursor usage
    LET C2 CURSOR
    FOR
      SELECT
        col1
      FROM
        MyTemporaryTable
      WHERE
        col1 = 1;
    prepareQuery_aux_sql := SQL_string_sel;
    S1 := (
      EXECUTE IMMEDIATE prepareQuery_aux_sql
    );
    LET CURSOR_S1_INSTANCE_V0 CURSOR
    FOR
      S1;
    OPEN CURSOR_S1_INSTANCE_V0;
    FETCH
      CURSOR_S1_INSTANCE_V0
    INTO
      intermediate_result;
    CLOSE CURSOR_S1_INSTANCE_V0;
    procedure_result := intermediate_result;
    INSERT INTO databaseTest.MyStatusTable (Col1)
    VALUES (procedure_result);
    OPEN C2;
    FETCH
      C2
    INTO
      intermediate_result;
    CLOSE C2;
    procedure_result := procedure_result + intermediate_result;
  END;
$$;

CALL databaseTest.simple_scenario();
SELECT * FROM MyStatusTable;

Ausgabe¶


Col1
1

Vorbereiten kombiniert mit verschachtelten Cursors¶

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE fetch_nested_cursor()
BEGIN
    --Variables for the example's procedure_results
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT col1 FROM MyTemporaryTable WHERE col1 = 1';
    DECLARE intermediate_result INTEGER;
    DECLARE C2 CURSOR FOR SELECT col1 FROM MyTemporaryTable WHERE col1 = 1;

    -- Actual Cursor usage
    DECLARE C1 CURSOR FOR S1;
    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
    OPEN C2;
    FETCH C2 INTO intermediate_result;

    CLOSE C2;
    FETCH C1 INTO intermediate_result;
    CLOSE C1;
END;

Ausgabe¶

No returning information.

Snowflake Scripting ¶

Bemerkung

Verwendungen für Cursors müssen umbenannt und neu deklariert werden.

Abfrage¶

 CREATE OR REPLACE PROCEDURE fetch_nested_cursor ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ ""origin"": ""sf_sc"", ""name"": ""snowconvert"", ""version"": {  ""major"": 0,  ""minor"": 0,  ""patch"": ""0"" }, ""attributes"": {  ""component"": ""none"",  ""convertedOn"": ""01/01/0001"" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    --Variables for the example's procedure_results
    SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   col1 FROM
   MyTemporaryTable
WHERE col1 = 1';
    intermediate_result INTEGER;
    S1 RESULTSET;
    prepareQuery_aux_sql VARCHAR;
  BEGIN
     
     
    -- Actual Cursor usage
    LET C2 CURSOR
    FOR
      SELECT
        col1
      FROM
        MyTemporaryTable
      WHERE
        col1 = 1;
    prepareQuery_aux_sql := SQL_string_sel;
    S1 := (
      EXECUTE IMMEDIATE prepareQuery_aux_sql
    );
    LET CURSOR_S1_INSTANCE_V0 CURSOR
    FOR
      S1;
    OPEN CURSOR_S1_INSTANCE_V0;
    OPEN C2;
    FETCH
      C2
    INTO
      intermediate_result;
    CLOSE C2;
    FETCH
      CURSOR_S1_INSTANCE_V0
    INTO
      intermediate_result;
    CLOSE CURSOR_S1_INSTANCE_V0;
  END;
$$;

Ausgabe¶

No returning information.

Variablenmarkierungen ohne Neuordnung der Variablen¶

Warnung

Dieser Fall wird noch nicht unterstützt.

Teradata ¶

Abfrage¶

 CREATE PROCEDURE PREPARE_ST_TEST()
BEGIN
    DECLARE ctry_list VARCHAR(100);
    DECLARE SQL_string_sel VARCHAR(255);
    DECLARE col_value NUMBER;

    DECLARE C1 CURSOR FOR S1;

    SET ctry_list = '';
    SET col_value = 1;
    SET SQL_string_sel = 'SELECT * FROM databaseTest.MyTemporaryTable where Col1 = ?';
    PREPARE S1 FROM SQL_string_sel;
    OPEN C1 USING col_value;
    FETCH C1 INTO ctry_list;
    IF (ctry_list <> '') THEN
        INSERT INTO databaseTest.MyStatusTable(col1) VALUES ('ok');
    END IF;
    CLOSE C1;
END;

CALL PREPARE_ST_TEST();
SELECT * FROM MyStatusTable;

Ausgabe¶


Col1
ok

Snowflake Scripting ¶

Bemerkung

Verwendungen für Cursors müssen umbenannt und neu deklariert werden.

Abfrage¶

 CREATE OR REPLACE PROCEDURE PREPARE_ST_TEST_MARKERS ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE 
        p1 RESULTSET;
        p1_sql VARCHAR DEFAULT '';
        
    BEGIN
        LET ctry_list VARCHAR(100);
        LET SQL_string_sel VARCHAR(255);
        LET col_value NUMBER(38, 18);
        LET S1 RESULTSET;
        
        ctry_list := '';
        
        col_value := 1;
        
        SQL_string_sel := 'SELECT * FROM MyTemporaryTable WHERE Col1 = ?';
        
        p1_sql := SQL_string_sel;
        S1 := (
            EXECUTE IMMEDIATE p1_sql USING (col_value)
        );
        LET C1 CURSOR FOR S1;
        
        OPEN C1;
            FETCH C1 INTO ctry_list;
        IF (RTRIM(ctry_list) <> '') THEN
            INSERT INTO MyStatusTable (col1)
            VALUES ('ok');
        END IF;
            CLOSE C1;
    END;
$$;

Ausgabe¶


Col1
ok

Variablenmarkierungen mit Neuordnung der Variablen¶

Warnung

Dieser Fall wird noch nicht unterstützt.

Bemerkung

Wenn es Variablen gibt, die zwischen der Anweisung PREPARE und dem Cursor OPEN in Teradata einen anderen Wert haben, müssen Sie diese Variable in Snowflake vor EXECUTE IMMEDIATE verschieben. Die Informationen zu den dynamischen Variablen werden also in dem Moment aktualisiert, in dem die dynamische Abfrage ausgeführt wird.

Teradata ¶

Abfrage¶

 CREATE PROCEDURE PREPARE_ST_TEST()
BEGIN
    DECLARE ctry_list VARCHAR(100);
    DECLARE SQL_string_sel VARCHAR(255);
    DECLARE col_name NUMBER;

    DECLARE C1 CURSOR FOR S1;

    SET ctry_list = '';
    SET col_name = 1;
    SET SQL_string_sel = 'SELECT * FROM databaseTest.MyTemporaryTable where Col1 = ?';
    PREPARE S1 FROM SQL_string_sel;
    SET col_name = 2; // change value before open cursor
    OPEN C1 USING col_name;
    FETCH C1 INTO ctry_list;
    IF (ctry_list <> '') THEN
        INSERT INTO databaseTest.MyStatusTable(col1) VALUES ('ok');
    END IF;
    CLOSE C1;
END;

CALL PREPARE_ST_TEST();
SELECT * FROM MyStatusTable;

Ausgabe¶

"MyStatusTable" should be empty.

Snowflake Scripting ¶

Bemerkung

Verwendungen für Cursors müssen umbenannt und neu deklariert werden.

Abfrage¶

 CREATE OR REPLACE PROCEDURE PREPARE_ST_TEST_MARKERS ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE 
        p1 RESULTSET;
        p1_sql VARCHAR DEFAULT '';
        
    BEGIN
        LET ctry_list VARCHAR(100);
        LET SQL_string_sel VARCHAR(255);
        LET col_value NUMBER(38, 18);
        LET S1 RESULTSET;
        
        ctry_list := '';
        
        col_value := 1;
        
        SQL_string_sel := 'SELECT * FROM MyTemporaryTable WHERE Col1 = ?';
        
        p1_sql := SQL_string_sel;

        col_value:= 2; // Move variable setting before the EXECUTE IMMEDIATE
        
        S1 := (
            EXECUTE IMMEDIATE p1_sql USING (col_value)
        );
                
        LET C1 CURSOR FOR S1;
        
        OPEN C1;
            FETCH C1 INTO ctry_list;
        IF (RTRIM(ctry_list) <> '') THEN
            INSERT INTO MyStatusTable (col1)
            VALUES ('ok');
        END IF;
            CLOSE C1;
    END;
$$;

CALL PREPARE_ST_TEST();
SELECT * FROM MyStatusTable;

Ausgabe¶

"MyStatusTable" should be empty.

Anonyme Blöcke - Deklaration außerhalb des Blocks¶

Warnung

Dieser Fall wird noch nicht unterstützt.

Teradata ¶

Abfrage¶

 REPLACE PROCEDURE anonymous_blocks_case(OUT procedure_result INTEGER)
BEGIN
    --Variables for the example's procedure_results
    DECLARE SQL_string_sel VARCHAR(200) DEFAULT 'SELECT col1 FROM MyTemporaryTable WHERE col1 = 1';

    -- Actual Cursor usage
    DECLARE C1 CURSOR FOR S1;
    DECLARE C2 CURSOR FOR S2;

    PREPARE S1 FROM SQL_string_sel;
    OPEN C1;
    FETCH C1 INTO procedure_result;
    CLOSE C1;

    BEGIN
        PREPARE S2 FROM SQL_string_sel;
        OPEN C2;
        FETCH C2 INTO procedure_result;
        CLOSE C2;
    END;

    OPEN C1;
    CLOSE C1;
END;

Ausgabe¶

No returning information.

Abfrage¶

 CREATE OR REPLACE PROCEDURE anonymous_blocks_case (
--                                                   OUT
                                                       PROCEDURE_RESULT INTEGER)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "none",  "convertedOn": "01/01/0001" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    --Variables for the example's procedure_results
    SQL_string_sel VARCHAR(200) DEFAULT 'SELECT
   col1 FROM
   MyTemporaryTable
WHERE col1 = 1';
    S1 RESULTSET;
    prepareQuery_aux_sql VARCHAR;
    S2 RESULTSET;
  BEGIN
    -- Actual Cursor usage
     
    prepareQuery_aux_sql := SQL_string_sel
    S1 := (
      EXECUTE IMMEDIATE prepareQuery_aux_sql
    );
    LET CURSOR_S1_INSTANCE_V0 CURSOR
    FOR
      S1;
    OPEN CURSOR_S1_INSTANCE_V0;
    FETCH
      CURSOR_S1_INSTANCE_V0
    INTO
      procedure_result;
    CLOSE CURSOR_S1_INSTANCE_V0;
 
    BEGIN
      prepareQuery_aux_sql := SQL_string_sel
      S2 := (
        EXECUTE IMMEDIATE prepareQuery_aux_sql
      );
      LET CURSOR_S2_INSTANCE_V# CURSOR
      FOR
        S1;
      OPEN CURSOR_S2_INSTANCE_V#;
      FETCH
        CURSOR_S2_INSTANCE_V#
      INTO
        procedure_result;
      CLOSE CURSOR_S2_INSTANCE_V#;
    END;
    
    OPEN CURSOR_S1_INSTANCE_V0; -- NAME REMAINS AS NEEDED IN LOGIC
    CLOSE CURSOR_S1_INSTANCE_V0;
    RETURN null;
  END;
$$;

Ausgabe¶

No returning information.

Bekannte Probleme¶

Überprüfen Sie sorgfältig verschachtelte Cursor und Konditionale, wenn dies der Fall ist.

Zugehörige EWIs ¶

Keine zugehörigen EWIs.

REPEAT¶

Übersetzungsreferenz zur Konvertierung der REPEAT-Anweisung von Teradata in Snowflake Scripting

Bemerkung

Einige Teile des Ausgabecodes wurden aus Gründen der Übersichtlichkeit weggelassen.

Beschreibung ¶

Die REPEAT-Anweisung von Teradata wird in die Syntax von Snowflake Scripting REPEAT übersetzt.

For more information, see the Teradata REPEAT documentation.

 [label_name:] REPEAT 
    { sql_statement }
    UNTIL conditional_expression
END REPEAT [label_name];

Beispielhafte Quellcode-Muster ¶

Teradata ¶

Repeat¶

 CREATE PROCEDURE repeatProcedure(OUT resultCounter INTEGER)
BEGIN
    DECLARE counter INTEGER DEFAULT 0;
   
    customeLabel: REPEAT 
    	SET counter = counter + 1;
	UNTIL 10 < counter    
    END REPEAT customeLabel;
   
    SET resultCounter = counter;
END;

CALL repeatProcedure(:?);

Ergebnis¶

|resultCounter|
|-------------|
|11           |

Snowflake Scripting ¶

Repeat¶

 CREATE OR REPLACE PROCEDURE repeatProcedure (RESULTCOUNTER OUT INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		counter INTEGER DEFAULT 0;
	BEGIN
		 
		REPEAT
			counter := counter + 1;
		UNTIL (10 < :counter)
		END REPEAT CUSTOMELABEL;
		resultCounter := counter;
	END;
$$;

CALL repeatProcedure(:?);

Ergebnis¶

|REPEATPROCEDURE|
|---------------|
|1             |

Bekannte Probleme ¶

Es wurden keine Probleme gefunden.

Zugehörige EWIs ¶

Keine zugehörigen EWIs.

SET¶

Übersetzungsreferenz zur Konvertierung der SET-Anweisung von Teradata in Snowflake Scripting

Bemerkung

Einige Teile des Ausgabecodes wurden aus Gründen der Übersichtlichkeit weggelassen.

Beschreibung ¶

Weist einer lokalen Variablen oder einem Parameter in einer gespeicherten Prozedur einen Wert zu.

For more information, see the Teradata SET documentation.

 SET assigment_target = assigment_source ;

Beispielhafte Quellcode-Muster ¶

Teradata ¶

Abfrage¶

 CREATE PROCEDURE setExample ( OUT PARAM1 INTEGER )
BEGIN
    DECLARE COL_COUNT INTEGER;
    SET COL_COUNT = 3;
    SET PARAM1 = COL_COUNT + 1;
END;

Ergebnis¶

|PARAM1 |
|-------|
|4      |

Snowflake Scripting¶

Abfrage¶

 CREATE OR REPLACE PROCEDURE setExample (PARAM1 OUT INTEGER )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        COL_COUNT INTEGER;
    BEGIN
         
        COL_COUNT := 3;
        PARAM1 := COL_COUNT + 1;
    END;
$$;

Ergebnis¶

|PARAM1 |
|-------|
|4      |

Bekannte Probleme ¶

Es wurden keine Probleme gefunden.

Zugehörige EWIs ¶

Keine zugehörigen EWIs.

SYSTEM_DEFINED¶

Bemerkung

Einige Teile des Ausgabecodes wurden aus Gründen der Übersichtlichkeit weggelassen.

Bemerkung

Nicht relevante Anweisung.

Warnung

Beachten Sie, dass diese Anweisung aus der Migration entfernt wurde, da sie eine nicht relevante Syntax darstellt. Das bedeutet, dass sie in Snowflake nicht erforderlich ist.

Beschreibung¶

Eigenschaft in Teradata, die in Fällen wie JOIN INDEX nach einer CREATE-Anweisung stehen kann.

Beispielhafte Quellcode-Muster¶

Beachten Sie, dass SYSTEM_DEFINED aus dem Quellcode entfernt wurde, da es sich um eine nicht relevante Syntax in Snowflake handelt.

Teradata¶

 CREATE SYSTEM_DEFINED JOIN INDEX MY_TESTS.MYPARTS_TJI004 ,FALLBACK ,CHECKSUM = DEFAULT, MAP = TD_MAP1 AS
CURRENT TRANSACTIONTIME 
SELECT
    MY_TESTS.myParts.ROWID,
    MY_TESTS.myParts.part_id,
    MY_TESTS.part_duration
FROM MY_TESTS.myParts
UNIQUE PRIMARY INDEX (part_id);

Snowflake¶

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TESTS.myParts" **
CREATE OR REPLACE DYNAMIC TABLE MY_TESTS.MYPARTS_TJI004
--** SSC-FDM-0031 - DYNAMIC TABLE REQUIRED PARAMETERS SET BY DEFAULT **
TARGET_LAG='1 day'
WAREHOUSE=UPDATE_DUMMY_WAREHOUSE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/01/2024" }}'
AS
--    --** SSC-FDM-TD0025 - TEMPORAL FORMS ARE NOT SUPPORTED IN SNOWFLAKE **
--    CURRENT TRANSACTIONTIME
                            SELECT
        MY_TESTS.myParts.ROWID,
        MY_TESTS.myParts.part_id,
        MY_TESTS.part_duration
    FROM
        MY_TESTS.myParts;

Bekannte Probleme ¶

Es wurden keine Probleme gefunden.

Zugehörige EWIs¶

[SSC-FDM-0007](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/generalFDM. md#ssc-fdm-0007): Element mit fehlenden Abhängigkeiten.
[SSC-FDM-TD0025](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/teradataFDM. md#ssc-fdm-td0025): Teradata Database Temporal Table wird in Snowflake nicht unterstützt.
[SSC-FDM-0031](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/generalFDM. md#ssc-fdm-0031): Erforderliche Parameter der dynamischen Tabelle standardmäßig eingestellt

WHILE¶

Übersetzungsreferenz zur Konvertierung der WHILE-Anweisung von Teradata in Snowflake Scripting

Bemerkung

Einige Teile des Ausgabecodes wurden aus Gründen der Übersichtlichkeit weggelassen.

Beschreibung ¶

Die WHILE-Anweisung von Teradata wird in die Syntax von Snowflake ScriptingWHILE übersetzt.

For more information, see the Teradata WHILE documentation.

 [label_name:] WHILE conditional_expression DO
    { sql_statement }
END WHILE [label_name];

Beispielhafte Quellcode-Muster¶

Teradata¶

While¶

 REPLACE PROCEDURE whileProcedure(OUT resultCounter INTEGER)
BEGIN
    DECLARE counter INTEGER DEFAULT 0;
    customeLabel: WHILE counter < 10 DO
        SET counter = counter + 1;
    END WHILE customeLabel;
    SET resultCounter = counter;
END;

CALL whileProcedure(:?);

Ergebnis¶

 |resultCounter|
|-------------|
|10           |

Snowflake Scripting ¶

While¶

 CREATE OR REPLACE PROCEDURE whileProcedure (RESULTCOUNTER OUT INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        counter INTEGER DEFAULT 0;
    BEGIN
         
        WHILE (:counter < 10) LOOP
            counter := counter + 1;
        END LOOP CUSTOMELABEL;
        resultCounter := counter;
    END;
$$;

CALL whileProcedure(:?);

Ergebnis¶

 |WHILEPROCEDURE|
|--------------|
|10            |

Bekannte Probleme ¶

Es wurden keine Probleme gefunden.

Zugehörige EWIs ¶

Keine zugehörigen EWIs.