Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
//====================================================================
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;
Maybe this Re: Partial Reload: REPLACE/ADD LOAD returns script errors before loading and this Re: Add, Add Only and Replace, Replce Only is helpful for you.
- Marcus
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
Good point.
Yes it is for partial reloading for Section Access so i can always load my latest security file even if the application data has not changed.
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;