Concepts in Bteq
Return Codes:

BTEQ return codes are two digit values that BTEQ will  return to the client operating system as a result of any error code that occured in BTEQ session. 

Some BTEQ return codes are as given below,

Return Codes & Description:

00    à Job completed with no errors.
02  à User alert to log on to the Teradata Database.
                  04  à Warining Error.                  08  à User Error.
                  12  à Server Internal Error.

The return code is  decided based on the  error messages that BTEQ gets from the Teradata database. 
Different Teradata database error codes will be assigned a specific return code value i
An Example for Script Structure:
            lets say the user wants to update the address of an employee in Employee table and wishes to increase the salary twice.
Employee table resides in the database named Company.BTEQ script:
            .Set Session Transaction Ansi
            .Logon TDUSER/PWD
            /*updating details of emp_no 101*/
            Update Company.Employee SET Address=’Rome’ Where emp_no=101;
            Update Company.Employee SET Salary=2*Salary Where emp_no=101;

Exporting results to a file:

Beow is the syntax to  export or write the output of Teradata SQL command to a local file by using EXPORT Bteq command.
.EXPORT FILE = <Local path>;

Example:    .LOGON LocalTD/DBC,DBC;
                    .Export file = C:\TX\Out.txt;
                    .SET SEPERATOR ‘|’
                    Database DB_NAME;
                    Select *from Tbl_Emp;
Result: The Select Stmt output is written into the file C:\TX\Out.txt
            Each column content will be separated by ‘|’

Run File &#40;Batch Mode&#41;:

To run batch of commands in BTEQ using the batch mode utility. 

             All required commands (both BTEQ and SQL) will be put in a file and the file will be supplied as input to BTEQ using RUN FILE command. 
            .RUN FILE = <Path of the Fire>

Example: Consider the content below present in a file C:\tx\sample.txt
1.       .LOGON localtd/dbc,dbc
2.       .EXPORT report  file=C:\tx\s1.xls;
3.       DATABASE forgetcode;
4.       SELECT * FROM tbl_emp;
5.       .EXPORT reset
6.       .LOGOFF
7.       .EXIT
In BTEQ, we can simply give the command as 

            .RUN FILE = C:\tx\sample.txt

Each Stmt of the file is executed and the output of select will be returned to the C:\tx\s1.xls

Creating and calling MACRO:

We can create or call a macro directly in a script.
After entering the login credentials EXECUTE,
now macro will be supplied.

        We can call the macro from BTEQ, which was already created or can be dynamically create it in BTEQ script itself.

            .Logon  localTD/Tduser,Pwd
             Execute  Macro;

Example 1:  Calling Macro which was already created,

            .Logon localtd/tduser,pwd
             Execute mc_employee;

Example 2) :In order to Create a macro dyanamically in the BTEQ script and calling it.

            .Logon localtd/tduser,pwd;
             Create Macro mc_emp as
             (Echo ‘.set Separator “|” ‘
              ;Select *from tbl_employee;
             Execute mc_emp;

Executing Stored Procedure:

call a stored procedure  in a BTEQ script directly  by rendering CALL command. 
                        CALL Stored Procedure ([parameters]);

              Calling the stored procedure sp_emp by passing  the name and employee number. 

                        .Logon Localtd/tduser/pwd;
                         CALL Sp_emp(‘Rufus’,12345);

Script Structure:

To create and execute the BTEQ script in Teradata we have to follow the below steps -

Step 1: Create the BTEQ Script
To create and edit a BTEQ script we could use an text editor or client workstation. 

                        .SET SESSION TRANSACTION ANSI
                        .LOGON TDUSER/TDPWD

Step 2: Executing the script
Start BTEQ, now enter the below  BTEQ command to submit a BTEQ script:

                        .RUN FILE = <Bteqscriptname>

Note: Comments can also be included in a BTEQ
 ANSI style (--) can also be be used in any SQL command script 

Creating Run File:

            In BTEQ, commands could be put in a file and the file name will be given with RUN FILE command.
            Syntax:          .Run file =Filename    Or
                        .Run DDNAME Filename;
Suppose, the below content is in a file C:\Sample.txt
.Logon Tdpid/ C:\Sample.txt         Or
                        .Run DDNAME C:\Sample.txt
            ‘=’ is Optional