All Forums Tools
Kasher 1 post Joined 03/05
18 Apr 2007
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 WHEREcust_date between Control_table.run_date -2 and control_table.run_date;.export resetupdate control_Tableset 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_nameAny solution?

joedsilva 505 posts Joined 07/05
18 Apr 2007

If you were using unix, you could resort to Shell variables.#!/usr/bin/kshrun_date=`date '+%Y%m%d'`bteq <<-END.export report file=${run_date}.export_file_name;END

al1_24 39 posts Joined 11/04
19 Apr 2007

You need two bteq scripts inside your Unix shell script :# Prepare the tmp.sh script and get the run_date valuebteq << first.logon ??.export report file ='tmp.sh'SELECT 'export run_date=' || (run_date (format '??'))FROM control_table;.export reset.quit 0first# Execute the tmp.sh script wich sets the run_date variablechmod +x tmp.shtmp.shrm tmp.sh# Execute your export script bteq << second.logon ??.export report file ='${run_date}.export_file_name'....quit;second

BBR2 96 posts Joined 12/04
19 Apr 2007

Actually you could accomplish this within a single script as below via .OS command.export report file = export_file_nameSelect ****FROM *,*,*,*,control_table WHEREcust_date between Control_table.run_date -2 and control_table.run_date;.export resetupdate control_Tableset run_date = run_date +2;.OS mv export_file_name export_file_name.`date '+%Y%m%d'`.logoff.quitVinay

You must sign in to leave a comment.