1: Export the output of the table in such a format that it beomces executable script and then call that script .

2: Use Export Reset command to achieve the same in one script.

Hi Jigar,

I am able to write  a simeple bteq script,Could you please help me to convert the exported output to a executable script.Also I am unable to understand  how export reset command is used in this scenario.

.RUN file=D:\bteq\conn.txt;
.export report  file=D:\bteq\s1.txt;
select * from bteq;
.export reset;
.logoff;

Sno   stmt

1    select count(*) from emp
2    select count(*) from bonus
2    select count(*) from dept_test
 

Thanks,

Avinash

What about executing the table content?I think This approach may work...try this..

.Logon Server/Userid,PWD

.export report  file=To_some_specified_directory

select * from Main_Table;

.export reset

.export report  file=To_Final_output_file

.run file = From_that_specified_directory

.export reset

.logoff

.quit

Thanks,

Banerjee

Hi All,

Based on the below script,I have encountered  below issue's.Pleasehelp me overcome them.

.RUN file=D:\bteq\conn.txt;
.export report  file=D:\bteq\s1.txt;
 select stmt from bteq;
.export reset;
.export report file=D:\bteq\s2.txt;
.run file=D:\bteq\s1.txt;
.export reset;
.logoff;

1.While doing an export,I am unable to eliminate column name. ie s1.txt will have column name as first line.

2.If i remove the column name manually and run ,then second export stmt is printing as below,need to eliminate the column heading from it.

Count(*)
-----------
         14
   Count(*)
-----------
          0
   Count(*)
-----------
          6

Thanks,

Avinash

You need to remove the column name using a TITLE:

 select stmt (TITLE '') from bteq;

Dieter

Thanks Diether.

Issue 1 is resolved ,where as for 2nd issue I am unable to insert a record as below.

It is not accepting the quotes after title 

insert into bteq(64, 'select count(*) (title '')from bonus;');

-Avinash

Hi Avinash,

each single quote within a string must be doubled:
insert into bteq(64, 'select count(*) (title '''')from bonus;');

But you might better use following aproach instead of EXPORT REPORT:

.EXPORT DATA;
.SET RECORDMODE OFF;

This also skips the column headers without modifying the string.

Dieter

hi Arya,

please use the below logic.

first export the data from the table to the file using the bewlo bteq script

.LOGON dbc/userame,pwd;
DATABASE dbname;;
.export data file=count.dat;
SELECT * FROM count_sql;
.export reset;
.logoff

import the data from the file and insert into another table with counts with the use of below bteq import script.

.logon server ip/username,pwd;
database dbname;
.import data file count.dat;
.repeat *

--consider there are 3 columns in your source table and same in output file.

using
 col1 (varchar(200))
,col2 (varchar(200))
,col3 (varchar(200))

--inserting into another table whcih will contains only counts

insert into venk_countdata
 SELECT tablename,COUNT(*) FROM
 (
 SELECT tablename FROM dbc.tables WHERE
 tablename= trim(SUBSTR(:col1 ,POSITION ('FROM' IN :col1)+4))
 )A group by 1;
.import data file count.dat;
.repeat *
using
 col1 (varchar(200))
,col2 (varchar(200))
,col3 (varchar(200))
insert into venk_countdata
 SELECT tablename,COUNT(*) FROM
 (
 SELECT tablename FROM dbc.tables WHERE
 tablename= trim(SUBSTR(:col2 ,POSITION ('FROM' IN :col2)+4))
 )A group by 1;
.import data file count.dat;
.repeat *
using
 col1 (varchar(200))
,col2 (varchar(200))
,col3 (varchar(200))
insert into venk_countdata
 SELECT tablename,COUNT(*) FROM
 (
 SELECT tablename FROM dbc.tables WHERE
 tablename= trim(SUBSTR(:col3 ,POSITION ('FROM' IN :col3)+4))
 )A group by 1;;
.quit;
.logoff;

let me know if you are not clear.

Thanks,

venkat

Hi,
 
I have to load a Date dimension table using sys_calendar using bteq,can some one please help me with a sample format, and how I could call the same using informatica.
TIA