Template Table

[Former Member]

Is it possible to create template tables within a while loop?

I have a need to create staging tables for all customers of a particular Accounting group type. I'm not sure how the naming of the template tables would be set dynamically?

Any assistance and direction is greatly appreciated.

Thank you!

[former_member208402 · Accepted solution]

Hi Jamie,

I guess we can implement this. But you need to have target tables created in DB with your desired name.

Take a script at the end after the dataflow in the while loop and write sql() function to insert data from temp table created to your Target table.

And write if condition based on the while loop condition you defined.

If suppose you while condition is like this  ($G_WHL_COND<=4) and you declared this variable as 1 initially and the value of this will be incremented for every loop.

Now write end script like this for the 1st loop

if($G_WHL_COND=1)

begin

sql('Data_store_name','select * into <tablename> from <temp_table>);

end

for the 2nd loop

if($G_WHL_COND=2)

begin

begin

sql('Data_store_name','select * into <tablename> from <temp_table>);

end

If you want to create 4 tables write if conditions 4 times and for each if condition "From table will be your temp table created in DF" and "into table should be your expected target table name"

Make sure you increment $G_WHL_COND in this script after all the if conditions.

Thanks,

Ravi kiran

[former_member187605 · Accepted solution]

Instead of copying the template table to another one, it's much better to simply rename it. Will run much faster with larger tables. If you do so, you don't have  to create the target tables upfront either.

[former_member208402 · Accepted solution]

Hi Dirk,

DB wont allow duplicate table right. Like if i run this job today and say my target temp table is <Table_1>

i will write the end script or post load command to rename this table to <table_account> and this will create a table with name <table_account>

and i will run this job again tomorrow and it will try to rename this temp table <table_1> with the name <table_account> which is already present in the DB.. It will throw error..

So what would be the best solution for this... droping <table_account> before the DF starts and rename this <table_1> after the DF completes?

Thanks,

Ravi kiran.

[former_member187605 · Accepted solution]

Yes, you're right, duplicate names are not possible in a database. But this can be solved by dynamically changing the name of the target table.

E.g. you want a different table very day:

     sql(<datastore name>,'rename table table_1 to table_acccount_'||to_char(sysdate(),'YYYYMMDD'));

[former_member208402 · Accepted solution]

Thank you Dirk,

So is my solution correct if in case  we want to load data to same target table daily?

[former_member187605 · Accepted solution]

Yes, if you want to overwrite the table very time. But, in fact, then you don't need to do anything special in a script. If you simply name your template table table_acccount, that'll do the trick already.

[former_member208402 · Accepted solution]

Thanks for the reply Dirk.

But here the requirement is like this

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

Is it possible to create template tables within a while loop?

I have a need to create staging tables for all customers of a particular Accounting group type. I'm not sure how the naming of the template tables would be set dynamically?

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

So which would be best solution..?

Taking a case transform and filtering particular groups and taking temp tables for each output produced from case?

[former_member187605 · Accepted solution]

Sure, then you do as I said before. You want a different table per accounting group:

     sql(<datastore name>,'rename table table_1 to table_acccount_[$Group]');

with $Group a global variable that you change for every iteration in the loop.

[Former Member · Accepted solution]

Thank you Dirk and Ravi! 

The end result that I'm looking for is to dynamically overwrite the table data on a daily basis for each $Group Table,  This tables will be used for base tables within Information Steward so one view can be shared.

So Ravi's solution will override all data in the existing table without a delete statement correct?

[former_member187605 · Accepted solution]

If you want multiple tables, then loop:

1. data flow to write into template table
2. sql(<datastore name>,'drop table_acccount_[$Group]');
3. sql(<datastore name>,'rename table template to table_acccount_[$Group]');

[Former Member · Accepted solution]

Thanks Dirk,

One more question and hopefully the last.. 

When you say "data flow to write into template table".   Do you have a visual example of what this looks like in DS?  I'm currently scripting this whole thing and if there is a better way to do this it might make more sense for me to see an example... 

[former_member187605 · Accepted solution]

This is what I have been thinking off all the time (you really got me confused 😞

In Init you set your counter to 0. The While-loop contains:

Before the data flow you set $Plant_nbr. The data flow loads the template table, using $Plant_nbr as a filter.

After the data flow you drop and rename tables and increment the counter.

[Former Member · Accepted solution]

Thank you Dirk!  You Rock!