2 Replies Latest reply: Feb 28, 2017 3:41 AM by Marcus Sommer RSS

    Replace loop table

    Ivan Drago

      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

        • Re: Replace loop table
          Hans Henrik Eriksen

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

            • Re: Replace loop table
              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