Bteq script to read and execute query from a table

General
Fan

Bteq script to read and execute query from a table

Hi,

 I have a table with N number of records with following format:





col1 col2 col3 col4
sel count(*) from  Table_S1 sel count(*) from  Table_T1 x x
sel count(*) from  Table_S2 sel count(*) from  Table_T2 x x
sel count(*) from  Table_S3 sel count(*) from  Table_T3 x x
sel count(*) from  Table_S4 sel count(*) from  Table_T4 x x
sel count(*) from  Table_S5 sel count(*) from  Table_T5 x x

My requirement is I need to write a bteq script which reads row by row from this table until the last row, and execute the row content.

For eg:  I will read the first row from the table, I need to execute the content in col1 and col2 of row 1 and store it in to another table. This has to be continued till the last row in my table. Any way I can do this in Bteq script other than using shell scripting.

Thanks in advance.

10 REPLIES
Enthusiast

Re: Bteq script to read and execute query from a table

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.

Enthusiast

Re: Bteq script to read and execute query from a table

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

Enthusiast

Re: Bteq script to read and execute query from a table

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

Enthusiast

Re: Bteq script to read and execute query from a table

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

Senior Apprentice

Re: Bteq script to read and execute query from a table

You need to remove the column name using a TITLE:

 select stmt (TITLE '') from bteq;

Dieter

Enthusiast

Re: Bteq script to read and execute query from a table


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

Senior Apprentice

Re: Bteq script to read and execute query from a table

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

Enthusiast

Re: Bteq script to read and execute query from a table

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

Re: Bteq script to read and execute query from a table

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