Learn BTEQ ( Basic TEradata Query)

Introduction:

•Batch-mode utility for submitting SQL requests to the Teradata database.
•Runs on every supported platform — laptop to mainframe.
•Flexible and easy-to-use report writer.
•Exports data to a client system from the Teradata database:
–As displayable characters suitable for reports, or
–In native host format, suitable for other applications.
•Reads input data and imports it to the Teradata database as INSERTs, UPDATEs or DELETEs.
•Limited ability to branch forward to a LABEL, based on a return code or an activity count.
•BTEQ does error reporting, not error capture.
A Sample Code Part Using BTEQ Conditional Logic:

The Bank offers a number of special services to its Million-Dollar customers.
-------------------------------------------------------------
DELETE FROM Million_Dollar_Customer ALL;
.IF ERRORCODE = 0 THEN .GOTO TableOK
CREATE TABLE Million_Dollar_Customer
(Account_Number INTEGER
,Customer_Last_Name VARCHAR(20)
,Customer_First_Name VARCHAR(15)
,Balance_Current DECIMAL(9,2));
.LABEL TableOK
INSERT INTO Million_Dollar_Customer
SELECT A.Account_Number
,C.Last_Name
,C.First_Name
,A.Balance_Current
FROM Accounts A INNER JOIN
Account_Customer AC INNER JOIN
Customer C
ON C.Customer_Number = AC.Customer_Number
ON A.Account_Number = AC.Account_Number
WHERE A.Balance_Current GT 1000000;
.IF ACTIVITYCOUNT > 0 THEN .GOTO Continue
.QUIT
.LABEL Continue
-------------------------------------------------------------

What it does???

DELETE all rows from the Million_Dollar_Customer table.
IF this results in an error (non-zero), THEN create the table, ELSE attempt to populate using INSERT/SELECT.
IF some rows are inserted (ACTIVITYCOUNT>0) THEN arrange services, ELSE terminate the job.

Error Handling in BTEQ:

You can assign an error level (SEVERITY) for each error code returned and make decisions based on the level you assign.




For Example




.SET ERRORLEVEL 2168 SEVERITY 4,
(2173, 3342, 5262) SEVERITY 8
.SET ERRORLEVEL UNKNOWN SEVERITY 16
SELECT
. . . . . . . . . . . . . FROM. . . . . . . . . . . . . . . . ;
.IF ERRORLEVEL >= 14 THEN .QUIT 17 ;




To be Continued ................