All Forums Tools
21 Feb 2006
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.

22 Feb 2006

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 Abrahim2342 , 4543 , Bipasha Basu.......not with any sql query.21/02/204 select emplno ,',', deptno ,',', Empname from Employee page no.. 1Emplno ',' Deptno ',' Empname----- --- ------ --- ------------2134 , 3434 , John Abrahim2342 , 4543 , Bipasha Basu.......

XTUPIE 42 posts Joined 11/05
23 Feb 2006

HII 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 133select 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////////////////////////////////////////////////////////////////////////////////////////////

Regards
Divvy

23 Feb 2006

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.

Barry-1604 176 posts Joined 07/05
23 Feb 2006

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 bteq2) Do a ".set titledashes off" in bteq3) After each column, put a "(title '')" clause. Example: Select col1 (title '')...This should get rid of the extranneous things.Barry

sethiyagu 19 posts Joined 11/04
23 Feb 2006

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

BBR2 96 posts Joined 12/04
05 Mar 2006

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

You must sign in to leave a comment.