Adding count of Query result in Footing of BTEQ SCRIPT

[DFW1999]

Hi,

I am running a BTEQ script in which I am appending heading as follows:

.set heading 'HDR99001||&date'
it appends at the header this:

HDR001 03/28/2004


then I have my sql which outputs 100 records it can be > 100 or <100 based on the data(any number)

at the end of sql output I want to add a footing which should have the following syntax:
'TRL99000000( where 000000 should be replaced with actual count of records received in the above sql.
For Example if I get 100 records then the footing should be as follows:

TRL99000100

if records count is 88 then

TRL99000088

Any solution please?

thanks

 

[dnoeth]

Did you try to add a WITH-clause to your query?

sel * from tab
with 'TRL99' || trim(count(*) (format '999999')) (title '');

Dieter

 

[DFW1999]

Hello dnoeth,

Thanks for your tip regarding adding count(*) at the end of query result output.
I tried the following script:

.EXPORT REPORT FILE = FILE_NAME.TXT

.set defaults on;
.set format on;
.set heading '00HDR0001||&date||00001';
.set width 80;
.set separator ","
.set titledashes off;
.set pagelength 2900;

Select col1, col2, col3, col4
FROM table_A
WITH '99TRL'||trim(count(*) (format '999999999999'))(title '');
.set format off;
.EXPORT RESET;
.logoff;

I am getting total 2409 rows in the result set but the Trailer comes up like this:

99TRL000107002886

Any clue?

thanks

 

[dnoeth]

Strange...

Teradata BTEQ 08.02.01.00 for WIN32.
BTEQ -- Enter your DBC/SQL request or BTEQ command:
Select *
FROM dbc.dbcinfo
WITH '99TRL'||trim(count(*) (format '999999999999'))(title '');

*** Query completed. 3 rows found. 2 columns returned.
*** Total elapsed time was 10 seconds.


InfoKey InfoData
------------------------------ --------------------------------------------
RELEASE V2R.05.01.00.03
VERSION 05.01.00.03
99TRL000000000002


Maybe you should try a different BTEQ version?

Dieter