Loop a set of queries for a month and append the result in one file (Teradata)

Database

Loop a set of queries for a month and append the result in one file (Teradata)

Hi all ,

 

I am new to Teradata and i got a set of queries with dates in WHERE condition . All i need to do is run these set of queries (3 of them ) for a month.I did googled it and found that procudres are the way ahead. Buit  i dont know how to start with it . 

ex:

for date :'2018-01-09' 

sel * from etl.table where date_ext='2018-01-09' ;

sel * from etl.table_a where date_ext='2018-01-09' ;

sel * from etl.table_b where date_ext='2018-01-09' ;

 

------------------

for date :'2018-01-10' 

 

sel * from etl.table where date_ext='2018-01-10' ;

sel * from etl.table_a where date_ext='2018-01-10' ;

sel * from etl.table_b where date_ext='2018-01-10' ;

 

----------

 

1.i need to run the above query for one month giving with startdate and enddate as input.

2.after each loop  ,is it possible for me to create the result set  in one single file . for example ,i will be having 3  anserwsheets when i excute for a single date.now i  want all results of each date  to be appended in  one file with proper seggregation.is it possible ?

 

Please  help me with the logic and  the query structure  :)

Thanks & Regards

 


Accepted Solutions
Junior Contributor

Re: Loop a set of queries for a month and append the result in one file (Teradata)

Looks like you're usng SQL Assistant to send your Selects, there's no way to do loops in SQLA.

 

You might put the Select(s) in a Macro with a Date parameter and run it using a Named Parameter, which is prompted once when you press F5, enter '2018-01-10' (including the quotes):

EXEC myMacro(?indate);

 

Then cut&paste this 30 times and modify the input:

EXEC myMacro(?indate +1);

EXEC myMacro(?indate +2);

...

 

Or do it all from Excel (I'm not an Excel pro, so don't ask me how):

Press a button, connect to Teradata, submit your three queries for the full range of date.

Add some Excel programming to split the result in different tabs.

1 ACCEPTED SOLUTION
7 REPLIES
Junior Contributor

Re: Loop a set of queries for a month and append the result in one file (Teradata)

Why do you need a loop over one month instead of a single query with a range?

Which tool are you going to use and what export format do you want?

How large is your expected result set?

 

If those three Selects retuzrn the same columns you can simply UNION ALL them.

Re: Loop a set of queries for a month and append the result in one file (Teradata)

Hi dnoeth ,

 

Thanks for your reply .now  moving over  to your questions,

 

1.Why do you need a loop over one month instead of a single query with a range?.

    Yes.range would work  too. but  in my case , 3 queires has to be  executed one  by one . Right now what am doing is giving date to these queries, get the result ., copy paste the result set in an excel under the tab:2018-01-10 . for  each date , there are different tabs in excel .we have to paste the result set  and move on to next date and paste the result again in  the respective date tab .

 

2.Which tool are you going to use and what export format do you want?

Am using Teradata Version 14  and the format can be anything(csv/txt). i will copy paste the result set into excel later   .so  in my file it should look like ..

 

 

result set of first date 

..

..

..

-----appends----

 

result set of 2nd date 

..

..

.

 

----- 

3.How large is your expected result set?

small. max 10 records from  each query .

 

4.If those three Selects retuzrn the same columns you can simply UNION ALL them.

These tables have different structures and column name , none have  the same structure.

 

Trust this helps :)

Junior Supporter

Re: Loop a set of queries for a month and append the result in one file (Teradata)

Provided that definitions of table, table_a and table_b are identical, you can use simple UNION.

 

sel * from etl.table where date_ext BETWEEN DATE'2018-01-09' AND DATE'2018-02-09'
UNION ALL
sel * from etl.table_a where date_ext BETWEEN DATE'2018-01-09' AND DATE'2018-02-09'
UNION ALL
sel * from etl.table_b where date_ext BETWEEN DATE'2018-01-09' AND DATE'2018-02-09';

Teradata Frank, Certified Master

Re: Loop a set of queries for a month and append the result in one file (Teradata)

Hi,

 

structure of tables are different.

 

Thanks & Regards

Junior Supporter

Re: Loop a set of queries for a month and append the result in one file (Teradata)

In that case you will have to specify the columns in each select. The UNION requires that columns from each select match.

 

Teradata Frank, Certified Master
Junior Contributor

Re: Loop a set of queries for a month and append the result in one file (Teradata)

Looks like you're usng SQL Assistant to send your Selects, there's no way to do loops in SQLA.

 

You might put the Select(s) in a Macro with a Date parameter and run it using a Named Parameter, which is prompted once when you press F5, enter '2018-01-10' (including the quotes):

EXEC myMacro(?indate);

 

Then cut&paste this 30 times and modify the input:

EXEC myMacro(?indate +1);

EXEC myMacro(?indate +2);

...

 

Or do it all from Excel (I'm not an Excel pro, so don't ask me how):

Press a button, connect to Teradata, submit your three queries for the full range of date.

Add some Excel programming to split the result in different tabs.

Re: Loop a set of queries for a month and append the result in one file (Teradata)

I guess this solution will do the trick . Thanks :)

Tags (1)