Need to read date field from table in BTEQ

Tools
N/A

Need to read date field from table in BTEQ

Hi,

I have following bteq script to export some data on Unix enviornment. run_date I am getting from teradata Control date table:

.export report file ='export_file_name'

Select ****
FROM *,*,*,*,control_table
WHERE
cust_date between Control_table.run_date -2 and control_table.run_date
;

.export reset

update control_Table
set run_date = run_date +2;
.quit;

I want to attach Control_table.run_date to the export file name so my export file command would look like:

.export report file ='Control_table.run_date.export_file_name'

file name I would like to be for example: 20070418.export_file_name

Any solution?

3 REPLIES
Enthusiast

Re: Need to read date field from table in BTEQ

If you were using unix, you could resort to Shell variables.

#!/usr/bin/ksh

run_date=`date '+%Y%m%d'`

bteq <<-END

.export report file=${run_date}.export_file_name;

END
Enthusiast

Re: Need to read date field from table in BTEQ

You need two bteq scripts inside your Unix shell script :

# Prepare the tmp.sh script and get the run_date value
bteq << first
.logon ??
.export report file ='tmp.sh'
SELECT 'export run_date=' || (run_date (format '??'))
FROM control_table
;
.export reset
.quit 0
first

# Execute the tmp.sh script wich sets the run_date variable
chmod +x tmp.sh
tmp.sh
rm tmp.sh

# Execute your export script
bteq << second
.logon ??
.export report file ='${run_date}.export_file_name'
...
.quit;
second

Enthusiast

Re: Need to read date field from table in BTEQ

Actually you could accomplish this within a single script as
below via .OS command

.export report file = export_file_name

Select ****
FROM *,*,*,*,control_table
WHERE
cust_date between Control_table.run_date -2 and control_table.run_date
;

.export reset

update control_Table
set run_date = run_date +2;

.OS mv export_file_name export_file_name.`date '+%Y%m%d'`

.logoff
.quit

Vinay