I am working on peoplesoft deliverd jobs.One of them is taking more than 3 hours to insert 100,000 records because
references to lookups are directly from oracle table.I changed the job to load data into hashfile now it is taking 10 minutes
but we are loosing data for few fields.
here is the scenerio
I am giving example to one Employee Id
this is lookup query
Code: Select all
SELECT LTRIM(RTRIM(A.EMPLID)),LTRIM(RTRIM(A.SRC_SYS_ID)),
%DateTimeOut(A.SMOKER_DT), LTRIM(RTRIM(A.SMOKER))
FROM PS_PERS_SMOKER A
WHERE LTRIM(RTRIM(A.EMPLID))= ? AND
LTRIM(RTRIM(A.SRC_SYS_ID))= ? AND
A.SMOKER_DT=(SELECT MAX(F.SMOKER_DT) FROM PS_PERS_SMOKER F
WHERE A.EMPLID= F.EMPLID AND
A.SRC_SYS_ID= F.SRC_SYS_ID AND
F.SMOKER_DT <= %DateTimeIn(?)
)
records from source table are like this
Code: Select all
Emplid efftdt src_sys_id
xxxx 1980-01-01 HR
xxxx 1999-12-04 HR
Record from lookup table is
Code: Select all
emplid, effdt , src_sys_id, Smoker
xxxx , 1980-01-01, HR, N
Code: Select all
Emplid efftdt src_sys_id Smoker
xxxx 1980-01-01 HR N
xxxx 1999-12-04 HR N
but with changed job, first it puts all records in hash file, so I am not getting the value of smoker to the second record
Code: Select all
Emplid efftdt src_sys_id Smoker
xxxx 1980-01-01 HR N
xxxx 1999-12-04 HR
Can you please tell me how to get this when we use hash file.Do we get runtime value from source even if we use hashfile
Thanks