Very often when working with BTEQ script you will face requirement to use dynamic variables or parameters. This is not possible in plain SQL without using PL/SQL construct in Teradata. However if you are calling BTEQ code in shell script you can easily use shell variables in BTEQ SQL queries. Let’s quickly see an example to understand this. I have below BTEQ script:
Filename: abc.bteq
.logon 192.168.100.100/dbc,dbc select InfoKey,InfoData from dbc.dbcinfo; .quit
Command to run: bteq < abc.bteq
Now you can see I have hardcoded SQL query in the BTEQ. What if I want to populate column name , database name or tablename as the parameter. So I can create a shell script and pass the values as parameter. Let’s look at the example below:
#!/bin/sh col1=InfoKey col2=InfoData dbname=dbc tblname=dbcinfo bteq << lbl_btq .logon 192.168.100.100/dbc,dbc select $col1,$col2 from $dbname.$tblname; .quit lbl_btq
Command to run: sh -x run_BTEQ.sh
In the above script I have created 4 variables with column names, database name, tablename and using it directly in the BTEQ query. If you want to pass the values as command line argument then you can use below shell script.
#!/bin/sh col1=$1 col2=$2 dbname=$3 tblname=$4 bteq << lbl_btq .logon 192.168.100.100/dbc,dbc select $col1,$col2 from $dbname.$tblname; .quit lbl_btq Command to run: sh -x run_BTEQ.sh InfoKey InfoData dbc dbcinfo Output may look below: $# sh -x run_BTEQ.sh InfoKey InfoData dbc dbcinfo + col1=InfoKey + col2=InfoData + dbname=dbc + tblname=dbcinfo + bteq BTEQ 15.00.00.00 Thu May 30 13:01:05 2019 PID: 26852 +---------+---------+---------+---------+---------+---------+---------+---- .logon 192.168.100.100/dbc, *** Logon successfully completed. *** Teradata Database Release is 15.00.00.08 *** Teradata Database Version is 15.00.00.07 *** Transaction Semantics are BTET. *** Session Character Set Name is 'ASCII'. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---- select InfoKey,InfoData from dbc.dbcinfo; *** Query completed. 3 rows found. 2 columns returned. *** Total elapsed time was 1 second. InfoKey InfoData ------------------------------ -------------------------------------------- LANGUAGE SUPPORT MODE Standard RELEASE 15.00.00.08 VERSION 15.00.00.07 +---------+---------+---------+---------+---------+---------+---------+---- .quit *** You are now logged off from the DBC. *** Exiting BTEQ... *** RC (return code) = 0
Let me know if you need more inputs.