Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the below script which loops through a list of sql tables that I have listed on the I previous sheet script and stores this into a QVD. Some of the table names I want to replace a finding word with an empty text, but I am getting a error when I tried the below amendment, any ideas?
//Current script
LET vDataStoreDir = '..\02_QVD\';
FOR i = 0 TO NoOfTables() - 1
LET d = TableName(i);
STORE '$(d)' INTO '$(vDataStoreDir)'S1-'$(d).qvd';
NEXT
LET j = nooftables();
DO WHILE j > 0
LET d = TableName(0);
DROP TABLE '$(d)';
LET j = nooftables();
LOOP
//Amended script
LET vDataStoreDir = '..\02_QVD\';
FOR i = 0 TO NoOfTables() - 1
LET d = TableName(i);
STORE replace('$(d)','fact','') INTO '$(vDataStoreDir)'S1-'$(d).qvd';
NEXT
LET j = nooftables();
DO WHILE j > 0
LET d = TableName(0);
DROP TABLE '$(d)';
LET j = nooftables();
LOOP
Don't think you can use a function directly as the table argument to STORE.
I would suggest changing the 'LET d =' instead of the STORE:
LET d = TableName(i);
LET t = replace(d,'fact','');
STORE '$(t)' INTO '$(vDataStoreDir)'S1-'$(d).qvd';
The latter can perhaps be written as follows for clarity:
STORE [$(t)] INTO [$(vDataStoreDir)S1-$(d).qvd];
Must it not be:
STORE '$(d)' INTO '$(vDataStoreDir)'S1-'$(t).qvd';
Another approach could be the following:
FOR i = 0 TO NoOfTables() - 1
LET source = TableName(i);
LET target = replace(TableName(i),'fact','');
Rename table $(source) to $(target);
STORE '$(target)' INTO '$(vDataStoreDir)'S1-'$(target).qvd';
NEXT
- Marcus