CODEWITHSUNDEEP
unixteradata
Pulkit Sharma
Pulkit Sharma

Reputation: 381

How to capture bteq command result to variable without using flat file

Environment : Unix, DB : Teradata Using bteq command, I want to capture result of a select statement in a variable

Eg: select count(*) from mytable Store the result in variable so I can do an if else on the variable later.

I do not want to store a single variable in a file and then read it.

Is there any way to accomplish this without using flat file?

Upvotes: 1

Views: 10980

Answers (3)

Juan Diego Godoy Robles
Juan Diego Godoy Robles

Reputation: 14975

Don't need a file, just an output filter:

query_td () {
bteq << EOBTQ |grep '^>' |sed -e "s/^>//"
.LOGON dsn/user_id,password;
SELECT   '>'||'Dummy test';

.LOGOFF;
.QUIT;
EOBTQ
}


$ var=$(query_td)
$ echo $var
Dummy test

Explanation

The trick is to add a additional char > at the beginning of the select statement:

SELECT '>'||'Dummy test';

grep '^>' filters the output coming out from bteq heredoc document so we just keep the result of the query.

The last move is to clean up the char > added to filter with sed -e "s/^>//".

Upvotes: 3

Rob Paller
Rob Paller

Reputation: 7786

Look into the following commands in the BTEQ Manual:

  • WIDTH - Set the width in characters for your EXPORT
  • FORMAT
  • TITLEDASHES - Column dashes underneath column headers if you return them
  • EXPORT - Output of just the SELECT query is written to flat file.

With the correct settings you should be able to capture just the value returned by SELECT COUNT(*) FROM MyTable; and then later in your shell script parse the flat file to use the value. Furthermore you could return multiple columns in a manner which could be fed into AWK and read as columns fairly easily.

Upvotes: 0

Raj
Raj

Reputation: 22956

Place your select query in a file and run the following shell script. You should have the output in log.txt from which you can parse the query output.

shell.sh:

bteq << EOF >> log.txt
.logon dsn/user_id,password;
.set width 2000;
.run FILE = /home/user_id/query.txt;
.logoff;
.quit;
EOF

Upvotes: 0

Related Questions