Thanks for the reply. Maybe it'll help if I include the whole script....
Basically, I'm trying to do an incremental load. The first run-through works and creates a qualified field name of HRMS_EMP.HRMS_AS_OF_DATE. Once the QVD is created, on the next run I want to load the deltas into the table HRMS_EMP table and store them in the QVD. The IF statement determines the start date of the loop based on the last HRMS_AS_OF_DATE from the data loaded from the QVD. With the script as-is below, I think I end up with 2 sets of fields in the table - one set qualified as HRMS_EMP.xxx and the other as EmpDataTemp.xxx, (I truncated the list of fields, but you get the idea...)
I have to loop through the SQL by date because I'm using a function to build a slowly changing dimensions table, and the function only gives data as per a certain date. I think maybe it has something to do with my table names and the loop and the fact that I'm not qualifying the field names, but if I qualify the field names in the loop I end up with multiple temp tables (EmpDataTemp-1. EmpDataTemp-2, etc...
So, maybe I'm going about this wrong? TIA for any pointers.
//*******************************************
vProcDate=vStartDate // Use Default Start Date from Config. Could change below if QVD exists.
// Check to see if EmpQVD Exists and load if it does
LET vTest=FileTime(vEmpQVD);
LET vEmpQVDExists = isnull(FileTime(vEmpQVD)); // if qvd exists then 0 else -1
IF $(vEmpQVDExists)=0 THEN
// Load Contents of Current Employee QVD into Temp Table
QUALIFY *;
UNQUALIFY EMPLOYEE_ID, MGR_ID1;
EmpDataTemp:
LOAD * FROM $(vEmpQVD) (qvd);
UNQUALIFY *;
//EXIT SCRIPT;
// Update the Start Date based on the last update in the QVD
// Get last HRMS_UPDATE_DATE from HRMS+. Not sure yet if I'm going to use this.
Temp:
Load LAST_UPDATE as MaxValue;
SQL SELECT MAX(HRMS_AS_OF_DATE) as LAST_UPDATE FROM fn_getEmpCoreAttributes(getdate(), default, default);
Let vLastHRMSUpdateDate=FieldValue('MaxValue',1);
Drop Table Temp;
//EXIT Script;
// Get last HRMS_UPDATE_DATE from HRMS_EMP
Temp:
Load Max(EmpDataTemp.HRMS_AS_OF_DATE) AS MaxValue RESIDENT EmpDataTemp;
Let vLastQVDUpdateDate=FieldValue('MaxValue',1);
Drop Table Temp;
//Set the Process Start Date to the last day in the QVD
LET vProcDate=vLastQVDUpdateDate;
ENDIF
DO WHILE vProcDate<Today()
//Where conditions using the HRMS+ IRS
LET vFromExpr='HRMSInterface.dbo.fn_getEmpCoreAttributes(' & Chr(39) & Date(vProcDate, 'MM/D/YYYY') & Chr(39) & ',default,default)';
LET vFromExpr=vFromExpr & ' WHERE CAST(UPDATED_DATE AS DATE)=' & Chr(39) & Date(vProcDate, 'MM/D/YYYY') & Chr(39); //Adds clause to only pull the modified entries from this date
// QUALIFY *;
// UNQUALIFY EMPLOYEE_ID, MGR_ID1;
EmpDataTemp:
LOAD HRMS_AS_OF_DATE,
PERSON_ID,
COMPANY_CODE,
EMPLOYEE_ID,
FULL_NAME,
FIRST_NAME,
MIDDLE_NAME,
LAST_NAME,
MGR_ID1
;
SQL SELECT HRMS_AS_OF_DATE,
PERSON_ID,
COMPANY_CODE,
EMPLOYEE_ID,
FULL_NAME,
FIRST_NAME,
MIDDLE_NAME,
LAST_NAME,
MGR_PERSON_ID1 AS MGR_ID1
FROM $(vFromExpr)
;
// UNQUALIFY *;
STORE EmpDataTemp INTO $(vEmpQVD) (qvd);
DROP Table EmpDataTemp;
//Let vProcDate=MonthEnd(AddMonths(vProcDate,1)); //Increment Processing Date by 1 Month
Let vProcDate=vProcDate+1; //Increment Processing Date by Day
LOOP
//Store the Temp tables into QVD file and Drop the Temp table
STORE EmpDataTemp INTO $(vEmpQVD) (qvd);
DROP Table EmpDataTemp;
//Reload the QVD File with the working Table Name
QUALIFY *;
UNQUALIFY EMPLOYEE_ID, MGR_ID1;
HRMS_EMP:
LOAD * from $(vEmpQVD) (qvd);
UNQUALIFY *;
EXIT SCRIPT;
