Dynamic hashfile

[Durga]

Hi ,

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

these 3 coluns are key columns

Record from lookup table is

Code: Select all

emplid,       effdt ,        src_sys_id,    Smoker

xxxx ,        1980-01-01,        HR,         N

When I run the original job it gives

Code: Select all

Emplid      efftdt        src_sys_id     Smoker

xxxx       1980-01-01         HR            N
xxxx       1999-12-04         HR            N

because it is taking effctive date from source table at runtime and doing join on 3 columns add puts Smoker value.

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

[WoMaWil]

Look through your jobs step by step. Maybe that somewhere in the lookup you have an other fieldtype or a blank so that the lookup is not met.

[ray.wurlod]

Writes to hashed files are destructive overwrites. That is, if the same key occurs more than once, the last one in wins.

[Durga]

Wolfgang and Ray thanks for your reply.


There is only one record with one emplid in the look up table.

here is the logic

records from source

Emplid efftdt src_sys_id

xxxx 1980-01-01 HR
xxxx 1999-12-04 HR

record from lookup

emplid, effdt , src_sys_id, Smoker

xxxx , 1980-01-01, HR, N


when first time joins source emplid and effctdt with lookup it gives

xxxx , 1980-01-01, HR N

for the second record , when we pass the effct date from source to lookup stage at runtime it gives max(effctdate) from lookup table.1980-01-01.there is only one record with that date so it gives again the same date and there is a match again and puts 'N' to second record also.
If I change the logic and add the hash file I am not getting the original result.Is there any way to do this logic with out direct lookup to oracle

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(?) 

Thanks
Durga