Bteq in Report format

Tools
Enthusiast

Bteq in Report format

Hi,
I am using the Bteq to export the report in a flat file in CSV format, but the o/p file is containing the SQL too as a first line,

FYI:- The O/P is requied to contain headings, hence I am using the REPORT format in .Export command. and If I am exporting the data with DATA format the sql is not present in the O/P file.

can any body give the solutions for it.
6 REPLIES
Enthusiast

Re: Bteq in Report format

I want to add more things that, o/p file should contain the column headings and correspoding column's data,

like below:-

Emplno ',' Deptno ',' Empname
----- --- ------ --- ------------
2134 , 3434 , John Abrahim
2342 , 4543 , Bipasha Basu
.......

not with any sql query.

21/02/204 select emplno ,',', deptno ,',', Empname from Employee page no.. 1

Emplno ',' Deptno ',' Empname
----- --- ------ --- ------------
2134 , 3434 , John Abrahim
2342 , 4543 , Bipasha Basu
.......
Enthusiast

Re: Bteq in Report format

HI

I suggest you set your export titles correctly for this to work. If you gave me more information, like table layout and sample data I could recreate it on my computer and send you the results.

Here is a BTEQ I use. Some explainations
.run file = c:\TeraBatch\LogonD.txt - is the file I use to do my logon, so basically it contains the line ".logon SYSTEM/USERID,PASSWORD;

TSPACE.OUT - I my output file, see below the BTEQ for an example.
.rtitle - Is my report title

///////////////////////////// BTEQ ////////////////////////////////////////////////////////
.run file = c:\LogonD.txt

.export report ddname=c:\TSPACE.OUT
.set format on
.set rtitle 'TERADATA DEVELOPMENT (NCR-4455) SPACE REPORT - DATABASE INFORMATION'
.set format on
.set width 133

select Databasename, Tablename,
sum(currentperm) (format '999,999,999,999,999,999') as "PERM", sum(peakperm) (format '999,999,999,999,999,999') as "PEAK"
from dbc.Tablesize
with sum(currentperm) (format '999,999,999,999,999,999') , sum(peakperm) (format '999,999,999,999,999,999') by DatabaseName
with sum(currentperm) (format '999,999,999,999,999,999') , sum(peakperm) (format '999,999,999,999,999,999')
order by Databasename,tablename
group by Databasename,tablename;

.export reset

.logoff
////////////////////////////////// OUTPUT //////////////////////////////////////////
Sample output, Remeber it is 133 wide.
======================================

06/02/10 TERADATA DEVELOPMENT (NCR-4455) SPACE REPORT - DATABASE INFORMATION Page 1

DatabaseName TableName PERM PEAK
------------------------------ ------------------------------ ----------------------- -----------------------
$NETVAULT_CATALOG CATALOG 000,000,000,000,203,776 000,000,000,000,203,776
----------------------- -----------------------
Sum(CurrentPerm) 000,000,000,000,203,776 000,000,000,000,203,776

Control ACCOUNT_XREF_REASON 000,000,000,000,004,096 000,000,000,000,004,096
Control AIRCRAFT 000,000,000,000,203,776 000,000,000,000,203,776
Control AIRCRAFT_CONFIG 000,000,000,000,070,656 000,000,000,000,070,656
Control AIRCRAFT_TYPE_CONFIG 000,000,000,000,010,240 000,000,000,000,010,240
Control AIRLINE_HUB 000,000,000,000,051,200 000,000,000,000,051,200
////////////////////////////////////////////////////////////////////////////////////////////

Enthusiast

Re: Bteq in Report format

Hi,

I cant give the actual field name and data, and I think the problem is not with data, but it is something in different format of report generating,
because when I am generating the report with "DATA" option in .EXPORT
it is not giving the sql and other attributes, but changing the report in "REPORT" Format the report 1st giving the date, then sql and then page #, which we dont want in the flat file.

please reply as soon as possible.

Enthusiast

Re: Bteq in Report format

If you want the results to be like "export report", but without the titles, etc., do the following:

1) Do a ".set format off" in bteq
2) Do a ".set titledashes off" in bteq
3) After each column, put a "(title '')" clause. Example:
Select col1 (title '')...

This should get rid of the extranneous things.

Barry
Enthusiast

Re: Bteq in Report format

Hi Manoj,

Please follow the steps to meet your requirements, you may specify file command in export rather than report command(as you have already mentioned "changing the report in "REPORT" Format the report 1st giving the date, then sql and then page #, which we dont want in the flat file"),

.logon dbcname/userid, password;
.set format off;
.export file=c:\manoj.csv;
select empno, empname, deptno from database name.table name;
.export reset;
.logoff;

Hope it helps!
Stration
Enthusiast

Re: Bteq in Report format

Try .EXPORT REPORT FILE = FILE.TXT;

Other than this there are several options that you can use for formatig. You may want to review B035-2414-093A "Basic Teradata Query"

Vinay