Replace loop table

[ivandrago]

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

[#2]

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];

[marcus_sommer]

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