How to add a parameter file in BTEQ Script

General

How to add a parameter file in BTEQ Script

Table name is employee_stg

Details:

EmployeeNo,FirstName,LastName,BirthDate,JoinedDate,DepartmentNo
101,Mike,shyam,1980-01-05,2010-03-01,1
102,shyam,Williams,1983-03-05,2010-09-01,1
103,Peter,Paul,1983-04-01,2009-02-12,2
104,Alex,Stuart,1984-11-06,2014-01-01,2
105,shyam,James,1984-12-01,2015-03-09,3
106,sai,shyam,1992-11-29,2017-11-29,4
107,ravi,Sankar,1987-12-12,2005-04-12,4

 

I am passing parameter file as

name1 =sai

 

BTEQ Script

.LOGON td-gdw-d-/sda001,pass1234;
 DATABASE DEV_GDW_SBX;

--Display Date and time of script execution
SELECT DATE,TIME;
.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;


UPDATE DEV_GDW_SBX.employee_stg
SET FirstName=${name1}
WHERE EmployeeNo=107;

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE

.LOGOFF

 

I want to update the firstname in a table when empno is 107.

Please help me.i am new to teradata

 

LOG FILE:

 


.LOGON td-gdw-d-/sda001,

 *** Logon successfully completed.
 *** Teradata Database Release is 15.10.04.04                  
 *** Teradata Database Version is 15.10.04.05                    
 *** Transaction Semantics are BTET.
 *** Session Character Set Name is 'ASCII'.
 
 *** Total elapsed time was 1 second.
 
+---------+---------+---------+---------+---------+---------+---------+----
 DATABASE DEV_GDW_SBX;

 *** New default database accepted.
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----

--Display Date and time of script execution
SELECT DATE,TIME;

 *** Query completed. One row found. 2 columns returned.
 *** Total elapsed time was 1 second.

    Date      Time
--------  --------
17/06/23  06:07:17

+---------+---------+---------+---------+---------+---------+---------+----
.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;
+---------+---------+---------+---------+---------+---------+---------+----


UPDATE DEV_GDW_SBX.employee_stg
SET FirstName=${name1}
WHERE EmployeeNo=107;
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE
.QUIT ERRORCODE
 *** You are now logged off from the DBC.
 *** Exiting BTEQ...
 *** RC (return code) = 3704

3 REPLIES
Teradata Employee

Re: How to add a parameter file in BTEQ Script

BTEQ does not do variable substitution. A common approach is to use a "here-document" for the script, so the shell can do the substitution before BTEQ reads it.

Re: How to add a parameter file in BTEQ Script

Hi Fred,
Can you share me the script. So it will be helpful for me to understand
Teradata Employee

Re: How to add a parameter file in BTEQ Script

A here-document is a standard Unix/Linux scripting technique, not specific to Teradata.

The BTEQ script would be in-line as part of the shell script rather than in a separate file, something like this:

 

bteq <<!EOF

.LOGON td-gdw-d-/sda001,pass1234;
 DATABASE DEV_GDW_SBX;

--Display Date and time of script execution
SELECT DATE,TIME;
.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;


UPDATE DEV_GDW_SBX.employee_stg
SET FirstName=${name1}
WHERE EmployeeNo=107;

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE

.LOGOFF

!EOF