4 Replies Latest reply: Sep 15, 2015 3:11 AM by Robert Evason RSS

    Replace load issue

      I want to use the following script in a Partial Reload, but if you add the REPLACE function the script doesn't perform the same way.

       

      Can anyone see why or a way around the problem?

       

      Without 'Replace' (works well)

      //====================================================================

      Table1:

      load * inline [

      ID,name,type,value

      1,A,blue,10

      1,A,yellow,50

      3,C,red,80

      3,C,yellow,60

      4,D,yellow,40

      ];

       

      Table2:

      generic load *

      resident Table1;

       

      ResultTable:

      load Distinct ID, name Resident Table1;

       

      FOR i = 0 to NoOfTables()

        TableList:

        load TableName($(i)) as Tablename AUTOGENERATE 1

        WHERE WildMatch(TableName($(i)), 'Table2.*');

      NEXT i

       

      FOR i = 1 to FieldValueCount('Tablename')

        LET vTable = FieldValue('Tablename', $(i));

        LEFT JOIN (ResultTable) load * RESIDENT [$(vTable)];

        DROP TABLE [$(vTable)];

      NEXT i

       

      Drop Tables Table1, TableList;

      //====================================================================

       

      With 'Replace' (doesn't work)

      //====================================================================

      Table1:

      replace load * inline [

      ID,name,type,value

      1,A,blue,10

      1,A,yellow,50

      3,C,red,80

      3,C,yellow,60

      4,D,yellow,40

      ];

       

      Table2:

      generic replace load *

      resident Table1;

       

      ResultTable:

      replace load Distinct ID, name Resident Table1;

       

      FOR i = 0 to NoOfTables()

        TableList:

        replace load TableName($(i)) as Tablename AUTOGENERATE 1

        WHERE WildMatch(TableName($(i)), 'Table2.*');

      NEXT i

       

      FOR i = 1 to FieldValueCount('Tablename')

        LET vTable = FieldValue('Tablename', $(i));

        LEFT JOIN (ResultTable) replace load * RESIDENT [$(vTable)];

        DROP TABLE [$(vTable)];

      NEXT i

       

      Drop Tables Table1, TableList;

      //====================================================================

        • Re: Replace load issue
          Donnie Clark

          Your issue is here:

           

          FOR i = 0 to NoOfTables()

            TableList:

            replace load TableName($(i)) as Tablename AUTOGENERATE 1

            WHERE WildMatch(TableName($(i)), 'Table2.*');

          NEXT i

           

          This doesn't build the list of generic tables because it is getting replaced during each iteration of the loop. So in the end you only end up with one record in this table and it only joins one of the generic tables to your final table.

           

          Is there a reason you are using the replace? Is it for partial reloading? I think because it is a table you end up dropping, you could use the add keyword instead of replace and be ok

          • Re: Replace load issue

            I amened the script and this seems to work well.

             

             

            IF IsPartialReload()=-1 THEN

                 DROP TABLE ResultTable;

            END IF

             

            Table1:

            replace load * inline [

            ID,name,type,value

            1,A,blue,10

            1,A,yellow,50

            3,C,red,80

            3,C,yellow,60

            4,D,yellow,40

            ];

             

            Table2:

            GENERIC REPLACE LOAD * RESIDENT Table1;

             

            ResultTable:

            REPLACE LOAD DISTINCT ID, name RESIDENT Table1;

             

            FOR i = 0 to NoOfTables() 

                 NoConcatenate

                 TableList$(i):

                 REPLACE LOAD TableName($(i)) AS Tablename AUTOGENERATE 1

                 WHERE WildMatch(TableName($(i)), 'Table2.*');

            NEXT i

             

            LET ii = $(i)-1;

             

            FOR i = 1 to FieldValueCount('Tablename')

                 LET vTable = FieldValue('Tablename', $(i));

                 LEFT JOIN (ResultTable) REPLACE LOAD * RESIDENT [$(vTable)];

                 DROP TABLE [$(vTable)];

            NEXT i

             

            DROP TABLE Table1;

             

            FOR i = 0 to ii

                 DROP TABLE TableList$(i);

            NEXT i;