Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have Created this procedure for Qvd Creation & Dropping original table but Dropping table function is not working can anyone give solution on it?
FOR i = 1 to NoOfTables();
let vtable=TableName($(i)-1);
let vname='$(vtable).qvd';
store $(vtable) into $(vtable).(qvd);
//DROP tables $(vtable) ;
NEXT i ;
I think that different things will NOT work:
1) DROP table $(vtable);
2) Because you drop table(s) into a loop, take care of index
TableName(counter) if the counter is incrementing and your tables decrementing, you will miss some tables
Same (but less sure) about NoOfTables() : I do not know if the number is evaluated once at the beginning or each time. In the second case, you need to store the number in a local variable before the loop.
Fabrice
Not quite sure if you want to drop all tables but if so then this is what I use.
let numTables = NoOfTables();
for i=1 to $(numTables)
let tt = TableName(0);
drop table [$(tt)];
next
Hi Nils,
It's Working but i need Single Procedure for Creating QVD & Dropping tables..?
I have created this statement:
//Load tables from SQL
for tabellen = 0 to NoOfRows('Database_tabellen') -1
let vDatabase_tabelnaam = peek('Table',tabellen,'Database_tabellen');
let vToQVDnaam = peek('QVD_naam',tabellen,'Database_tabellen');
set tablename = $(vToQVDnaam); //set variable for all additionale statements and logging
$(vToQVDnaam):
LOAD *;
SQL SELECT *
FROM $(databasename).$(databaseowner)."$(database_schema)$(vDatabase_tabelnaam)";
call store_routine;
TRACE ---------------------------------------------------------------------;
next tabellen
Where the store routine is called:
sub store_routine
store $(tablename) into QVD\$(tablename).qvd (qvd);
drop table $(tablename);
set tablename = ;
end sub
I load from an external txt file the table name and give it a QVD name, without spaces. You can do that inline also.
The main difference is that I use Peek to extract the next table(name).
Just change $(i)-1 to 0 :
FOR i = 1 to NoOfTables();
let vtable=TableName(0);
let vname='$(vtable).qvd';
store $(vtable) into $(vtable).(qvd);
DROP tables $(vtable) ;
NEXT i ;
Ioannis,
One question about the loop.
The numbers of loops is evaluated at the enter of the loop (as in VBA for ex) ? NoOfTables will change during the loop but the number of iteration will remain the same ?
Cheers
Fabrice
yes, number of iterations will remain the same.
it is just that when the 1st table is dropped, the remaining ones go one position back. So for example, if you have 2 tables, when you drop the first (tableName(0)), then the second will become first (tableName(1) will then be tableName(0)). That is why tableName(0) is dropped in every iteration.
Hi Loannis,
Thanx for the answer its working now..
Could you please tell me Can i Use subroutine within this Script..?
Yes you can use subroutines into the script
Just, you need to write the code BEFORE the call of it (for example a tab before that groups all the sub routines)
Fabrice