SnowConvert: Teradata Scripts to Snowflake SQL Translation Reference¶
Browse through the following pages to find more information about specific topics.
BTEQ¶
Description¶
Note
Some parts in the output code are omitted for clarity reasons.
Basic Teradata Query (BTEQ) is a general-purpose, command-based program that enables users on a workstation to communicate with one or more Teradata Database systems and to format reports for both print and screen output.
For more information regarding BTEQ, check here.
Sample Source Patterns¶
1. Basic BTEQ Example¶
The BTEQ content is relocated within an EXECUTE IMMEDIATE
block of to transfer the BTEQ script functionality to Snowflake SQL executable code.
All the DML and DDL statements inside BTEQ scripts are supported by SnowConvert and successfully translated to Snowflake SQL. The commands that do not have support yet, or do not have support at all, are being marked with a warning message and commented out.
-- Additional Params: -q SnowScript
.LOGON 0/dbc,dbc;
DATABASE tduser;
CREATE TABLE employee_bkup (
EmployeeNo INTEGER,
FirstName CHAR(30),
LastName CHAR(30),
DepartmentNo SMALLINT,
NetPay INTEGER
)
Unique Primary Index(EmployeeNo);
DROP TABLE employee_bkup;
.IF ERRORCODE <> 0 THEN .EXIT ERRORCODE;
.LOGOFF;
EXECUTE IMMEDIATE
$$
DECLARE
STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
BEGIN
-- Additional Params: -q SnowScript
--.LOGON 0/dbc,dbc
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'BTLogOn' NODE ***/!!!
null;
BEGIN
USE DATABASE tduser;
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
EXCEPTION
WHEN OTHER THEN
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
END;
BEGIN
CREATE OR REPLACE TABLE employee_bkup (
EmployeeNo INTEGER,
FirstName CHAR(30),
LastName CHAR(30),
DepartmentNo SMALLINT,
NetPay INTEGER,
UNIQUE (EmployeeNo)
);
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
EXCEPTION
WHEN OTHER THEN
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
END;
BEGIN
DROP TABLE employee_bkup;
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
EXCEPTION
WHEN OTHER THEN
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
END;
IF (STATUS_OBJECT['SQLCODE'] /*** SSC-FDM-TD0013 - THE SNOWFLAKE ERROR CODE MISMATCH THE ORIGINAL TERADATA ERROR CODE ***/ != 0) THEN
RETURN STATUS_OBJECT['SQLCODE'] /*** SSC-FDM-TD0013 - THE SNOWFLAKE ERROR CODE MISMATCH THE ORIGINAL TERADATA ERROR CODE ***/;
END IF;
--.LOGOFF
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'LogOff' NODE ***/!!!
null;
END
$$
2. Bash Variable Placeholders Example¶
SnowConvert supports the migration of BTEQ code with Bash Variable Placeholders used for shell scripts, these placeholders will be migrated to its SnowSQL equivalent and SSC-FDM-TD0003 will be added to the code. Please consider the following when migrating code with these placeholders:
SnowConvert does not support the migration of shell scripts, to migrate the BTEQ code please isolate it in a BTEQ file and supply it as input for the tool.
SnowSQL with variable substitution enabled is required to execute the migrated code, for more information on how to use SnowSQL please check SSC-FDM-TD0003 and the official documentation for SnowSQL.
-- Additional Params: -q SnowScript
.LOGON dbc, dbc;
DATABASE testing;
SELECT $columnVar FROM $tableVar WHERE col2 = $nameExprVar;
INSERT INTO $tableName values ('$myString', $numValue);
UPDATE $dbName.$tableName SET col1 = $myValue;
DELETE FROM $tableName;
.LOGOFF;
EXECUTE IMMEDIATE
$$
--** SSC-FDM-TD0003 - BASH VARIABLES FOUND, USING SNOWSQL WITH VARIABLE SUBSTITUTION ENABLED IS REQUIRED TO RUN THIS SCRIPT **
DECLARE
STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
BEGIN
-- Additional Params: -q SnowScript
--.LOGON dbc, dbc
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'BTLogOn' NODE ***/!!!
null;
BEGIN
USE DATABASE testing;
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
EXCEPTION
WHEN OTHER THEN
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
END;
BEGIN
SELECT
&columnVar
FROM
&tableVar
WHERE
col2 = &nameExprVar;
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
EXCEPTION
WHEN OTHER THEN
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
END;
BEGIN
INSERT INTO &tableName
VALUES ('&myString', &numValue);
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
EXCEPTION
WHEN OTHER THEN
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
END;
BEGIN
UPDATE &dbName.&tableName
SET
col1 = &myValue
;
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
EXCEPTION
WHEN OTHER THEN
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
END;
BEGIN
DELETE FROM
&tableName;
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
EXCEPTION
WHEN OTHER THEN
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
END;
--.LOGOFF
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'LogOff' NODE ***/!!!
null;
END
$$
Known Issues¶
There may be BTEQ commands that do not have an equivalent in Snowflake SQL
Since BTEQ is a command-based program, there may be some commands in your input code that do not have a hundred percent functional equivalence in Snowflake SQL. Those particular cases are identified, marked with warnings in the output code, and documented in the further pages.
MLOAD¶
Import¶
Description¶
The IMPORT command specifies a source for data input.
For more information regarding Import MLoad, check here
Sample Source Patterns¶
As BTEQ content also MLoad is relocated in an EXECUTE IMMEDIATE
block. Import transformation with take each layout field an added to a select. Inserts in dml label will be transform to COPY INTO and upserts (Update and insert) will be transform to MERGE INTO.
1. DML label with insert¶
-- Additional Params: -q SnowScript
.LOGTABLE my_table_log;
.LOGON my_teradata_system/username,password;
BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work
ERRORTABLES my_table_err;
.LAYOUT my_layout;
.FIELD col1 * VARCHAR(2);
.FIELD col2 * VARCHAR(5);
.dml label insert_into_my_table
IGNORE DUPLICATE INSERT ROWS ;
INSERT INTO my_table(col1, col2) VALUES (:col1, :col2);
.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
FORMAT UNFORMAT
LAYOUT my_layout APPLY insert_into_my_table;
.END MLOAD;
.LOGOFF;
EXECUTE IMMEDIATE
$$
DECLARE
STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
BEGIN
-- Additional Params: -q SnowScript
/*.LOGTABLE my_table_log;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*.LOGON my_teradata_system/username,password;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '7' COLUMN '1' OF THE SOURCE CODE STARTING AT 'ERRORTABLES'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'my_table_err' ON LINE '7' COLUMN '13'. CODE '81'. ***/
/*--ERRORTABLES my_table_err*/
/*.LAYOUT my_layout;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
/*.dml label insert_into_my_table
IGNORE DUPLICATE INSERT ROWS ;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
BEGIN
CREATE OR REPLACE STAGE &{stagename};
PUT file://C:\USER\user\my_tr_file_1.tr &{stagename};
COPY INTO my_table (
col1,
col2
)
FROM
(
SELECT DISTINCT
SUBSTRING($1, 1, 2) col1,
SUBSTRING($1, 3, 5) col2
FROM
@&{stagename}/my_tr_file_1.tr
);
END;
/*.END MLOAD;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*.LOGOFF;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
END
$$
2. DML label with upsert¶
-- Additional Params: -q SnowScript
.LOGTABLE my_table_log;
.LOGON my_teradata_system/username,password;
BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work
ERRORTABLES my_table_err;
.LAYOUT my_layout;
.FIELD col1 * VARCHAR(2);
.FIELD col2 * VARCHAR(5);
.dml label upsert_into_my_table;
UPDATE my_table
SET
col1 = :col1,
col2 = :col2
WHERE col2 = :col2
INSERT INTO my_table (
col1, col2)
VALUES (:col1, :col2);
.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
FORMAT UNFORMAT
LAYOUT my_layout APPLY upsert_into_my_table;
.END MLOAD;
.LOGOFF;
EXECUTE IMMEDIATE
$$
DECLARE
STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
BEGIN
-- Additional Params: -q SnowScript
/*.LOGTABLE my_table_log;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*.LOGON my_teradata_system/username,password;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '7' COLUMN '1' OF THE SOURCE CODE STARTING AT 'ERRORTABLES'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'my_table_err' ON LINE '7' COLUMN '13'. CODE '81'. ***/
/*--ERRORTABLES my_table_err*/
/*.LAYOUT my_layout;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
/*.dml label upsert_into_my_table;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
BEGIN
CREATE OR REPLACE STAGE &{stagename};
PUT file://C:\USER\user\my_tr_file_1.tr &{stagename};
MERGE INTO my_table merge_table
USING (
SELECT
SUBSTRING($1, 1, 2) col1,
SUBSTRING($1, 3, 5) col2
FROM
@&{stagename}/my_tr_file_1.tr
) load_temp ON merge_table.col2 = load_temp.col2
WHEN MATCHED THEN
UPDATE SET
merge_table.col1 = load_temp.col1,
merge_table.col2 = load_temp.col2
WHEN NOT MATCHED THEN
INSERT (col1, col2)
VALUES (load_temp.col1, load_temp.col2);
END;
/*.END MLOAD;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*.LOGOFF;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
END
$$
3. Layout or DML label not found¶
-- Additional Params: -q SnowScript
.LOGTABLE my_table_log;
.LOGON my_teradata_system/username,password;
BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work
ERRORTABLES my_table_err;
.LAYOUT my_layout;
.FIELD col1 * VARCHAR(2);
.FIELD col2 * VARCHAR(5);
.dml label insert_into_my_table
IGNORE DUPLICATE INSERT ROWS ;
INSERT INTO my_table(col1, col2)VALUES (:col1, :col2);
.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
FORMAT UNFORMAT
LAYOUT not_layout APPLY insert_into_my_table;
.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
FORMAT UNFORMAT
LAYOUT my_layout APPLY insert_not_my_table;
.END MLOAD;
.LOGOFF;pl
EXECUTE IMMEDIATE
$$
DECLARE
STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
BEGIN
-- Additional Params: -q SnowScript
/*.LOGTABLE my_table_log;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*.LOGON my_teradata_system/username,password;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '7' COLUMN '1' OF THE SOURCE CODE STARTING AT 'ERRORTABLES'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'my_table_err' ON LINE '7' COLUMN '13'. CODE '81'. ***/
/*--ERRORTABLES my_table_err*/
/*.LAYOUT my_layout;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
/*.dml label insert_into_my_table
IGNORE DUPLICATE INSERT ROWS ;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
-- .IMPORT INFILE C:\USER\user\my_tr_file_1.tr FORMAT UNFORMAT LAYOUT not_layout APPLY insert_into_my_table
;
BEGIN
CREATE OR REPLACE STAGE &{stagename};
PUT file://C:\USER\user\my_tr_file_1.tr &{stagename};
END;
/*.END MLOAD;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*.LOGOFF;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '26' COLUMN '9' OF THE SOURCE CODE STARTING AT 'pl'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'pl' ON LINE '26' COLUMN '9'. CODE '81'. ***/
/*--pl*/
END
$$
4. Conditions not found in update statement¶
-- Additional Params: -q SnowScript
.LOGTABLE my_table_log;
.LOGON my_teradata_system/username,password;
BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work
ERRORTABLES my_table_err;
.LAYOUT my_layout;
.FIELD col1 * VARCHAR(2);
.FIELD col2 * VARCHAR(5);
.dml label upsert_into_my_table;
UPDATE my_table
SET
col1 = :col1,
col2 = :col2
INSERT INTO my_table (
col1, col2)
VALUES (:col1, :col2);
.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
FORMAT UNFORMAT
LAYOUT my_layout APPLY upsert_into_my_table;
.END MLOAD;
.LOGOFF;
EXECUTE IMMEDIATE
$$
DECLARE
STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
BEGIN
-- Additional Params: -q SnowScript
/*.LOGTABLE my_table_log;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*.LOGON my_teradata_system/username,password;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '7' COLUMN '1' OF THE SOURCE CODE STARTING AT 'ERRORTABLES'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'my_table_err' ON LINE '7' COLUMN '13'. CODE '81'. ***/
/*--ERRORTABLES my_table_err*/
/*.LAYOUT my_layout;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
/*.dml label upsert_into_my_table;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
-- .IMPORT INFILE C:\USER\user\my_tr_file_1.tr FORMAT UNFORMAT LAYOUT my_layout APPLY upsert_into_my_table
;
/*.END MLOAD;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
/*.LOGOFF;*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
END
$$
Known Issues¶
Some statements from import clause where not supported yet:
AXSMOD statement
INMODE statement
FORMAT (only FORMAT UNFORMAT is supported)
WHERE condition of APPLY label
Related EWIS¶
SSC-EWI-0001: Unrecognized token on the line of the source code.
SSC-FDM-0027: Removed next statement, not applicable in SnowFlake.
COMMON STATEMENTS¶
ERROR HANDLING¶
The BTEQ error handling capabilities are based on the Teradata Database error codes. These are the standard error codes and messages produced in response to user-specified Teradata SQL statements. A BTEQ user cannot change, modify or delete these messages.
For more information regarding BTEQ Error Handling, check here.
Sample Source Patterns¶
Basic BTEQ Error Handling Example¶
The error conditions content is relocated in different statements in case ERRORCODE is different to zero, otherwise it can be located as the original code. First, the query above the if statement is relocated within a BEGIN - END block, where in case of an exception it will be caught in the EXCEPTION block. Second of all, the ERRORCODE variable will be changed to the variable declared indicating their SQLCODE with an EWI indicating that the exact number of the SQLCODE is not the same as the ERRORCODE in BTEQ.
-- Additional Params: -q SnowScript
SELECT * FROM table1;
.IF ERRORCODE<>0 THEN .EXIT 1
.QUIT 0
EXECUTE IMMEDIATE
$$
DECLARE
STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
BEGIN
BEGIN
-- Additional Params: -q SnowScript
SELECT
*
FROM
table1;
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
EXCEPTION
WHEN OTHER THEN
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
END;
IF (STATUS_OBJECT['SQLCODE'] /*** SSC-FDM-TD0013 - THE SNOWFLAKE ERROR CODE MISMATCH THE ORIGINAL TERADATA ERROR CODE ***/ != 0) THEN
RETURN 1;
END IF;
RETURN 0;
END
$$
Known Issues¶
No issues were found.
Related EWIs¶
SSC-FDM-TD0013: The Snowflake error code mismatch the original Teradata error code.
EXIT or QUIT¶
Logs off all database sessions and then exits BTEQ.
The highest severity value encountered during BTEQ’s execution will by default be used as BTEQ’s return code value unless an argument is explicitly supplied. (Teradata Basic Query Reference EXIT or QUIT Command)
.<ExitCommand> [<Result>];
<ExitCommand> := EXIT | QUIT
<Result> := <Status_variable> | Number
<Status_variable> := ACTIVITY_COUNT | ERRORCODE | ERRORLEVEL
Sample Source Patterns¶
Basic IF example¶
-- Additional Params: -q SnowScript
.QUIT ERRORCODE;
EXECUTE IMMEDIATE
$$
DECLARE
STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
BEGIN
RETURN STATUS_OBJECT['SQLCODE'] /*** SSC-FDM-TD0013 - THE SNOWFLAKE ERROR CODE MISMATCH THE ORIGINAL TERADATA ERROR CODE ***/;
END
$$
Known Issues¶
When the EXIT or QUIT command doesn’t have an input, it returns the ERRORLEVEL as default. However, SnowConvert transforms it to return 0.
Related EWIS¶
SSC-FDM-TD0013: The Snowflake error code mismatch the original Teradata error code.
GOTO¶
Description¶
The BTEQ Goto command skips over all intervening BTEQ commands and SQL statements until a specified label is encountered, then resumes processing as usual. (Teradata Basic Query Reference Goto Command)
.GOTO LabelName;
Sample Source Patterns¶
Basic GOTO example¶
Snowflake scripting doesn’t have an equivalent statement for Teradata BTEQ Goto command, but fortunately it can be removed from the input code and get an equivalent code, due to the sequence of Goto and Labels commands in reverse topological order always. In other words, the definitions come after their uses. Thus, Snowconvert just needs to copy bottom-up all Label section code to its corresponding Goto statements.
-- Additional Params: -q SnowScript
.LOGON 0/dbc,dbc;
DATABASE tduser;
.LOGON 127.0.0.1/dbc,dbc;
INSERT INTO TABLEB VALUES (1);
.IF activitycount = 0 then .GOTO SECTIONA
.IF activitycount >= 1 then .GOTO SECTIONB
.label SECTIONA
.REMARK 'Zero Hours on Account'
.GOTO SECTIONC
.label SECTIONB
.REMARK 'Total Hours on Account'
.label SECTIONC
.logoff
.exit
EXECUTE IMMEDIATE
$$
DECLARE
STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
BEGIN
-- Additional Params: -q SnowScript
--.LOGON 0/dbc,dbc
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'BTLogOn' NODE ***/!!!
null;
BEGIN
USE DATABASE tduser;
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
EXCEPTION
WHEN OTHER THEN
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
END;
--.LOGON
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'BTLogOn' NODE ***/!!!
null;
/*** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '4' COLUMN '8' OF THE SOURCE CODE STARTING AT '127.0'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'LOGON' ON LINE '4' COLUMN '2'. FAILED TOKEN WAS '127.0' ON LINE '4' COLUMN '8'. CODE '81'. ***/
/*--127.0.0.1/dbc,dbc*/
BEGIN
INSERT INTO TABLEB
VALUES (1);
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
EXCEPTION
WHEN OTHER THEN
STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
END;
IF (NOT (STATUS_OBJECT['SQLROWCOUNT'] = 0)) THEN
--** SSC-FDM-TD0026 - GOTO SECTIONA WAS REMOVED DUE TO IF STATEMENT INVERSION **
IF (STATUS_OBJECT['SQLROWCOUNT'] >= 1) THEN
/*.label SECTIONB*/
--.REMARK 'Total Hours on Account'
null;
/*.label SECTIONC*/
--.logoff
null;
RETURN 0;
END IF;
END IF;
/*.label SECTIONA*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
--.REMARK 'Zero Hours on Account'
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Remark' NODE ***/!!!
null;
/*.label SECTIONC*/
--.logoff
null;
RETURN 0;
/*.label SECTIONB*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
--.REMARK 'Total Hours on Account'
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Remark' NODE ***/!!!
null;
/*.label SECTIONC*/
--** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
--.logoff
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'LogOff' NODE ***/!!!
null;
RETURN 0;
END
$$
Known Issues ¶
No issues were found.
Related EWIS¶
SSC-EWI-0001: Unrecognized token on the line of the source code.
SSC-FDM-0027: Removed next statement, not applicable in SnowFlake.
SSC-EWI-0073: Pending Functional Equivalence Review
SSC-FDM-TD0026: GOTO statement was removed due to if statement inversion.
IF… THEN…¶
Description¶
The IF statement validates a condition and executes an action when the action is true. (Teradata SQL Language reference IF…THEN…)
.IF <Condition> THEN <Action>;
<Condition> := <Status_variable> <Operator> Number
<Status_variable> := ACTIVITY_COUNT | ERRORCODE | ERRORLEVEL
<Operator> := ^= | != | ~= | <> | = | < | > | <= | >=
<Action> := BTEQ_command | SQL_request
Sample Source Patterns¶
Basic IF example¶
-- Additional Params: -q SnowScript
.IF ACTIVITYCOUNT <> 0 THEN .GOTO InsertEmployee;
EXECUTE IMMEDIATE
$$
DECLARE
STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
BEGIN
IF (STATUS_OBJECT['SQLROWCOUNT'] != 0) THEN
RETURN 1;
END IF;
END
$$
Related EWIS¶
No related EWIs.