Hi,
I am using DB_LOOKUP to load my fact table from SCD dimensions , so when I look up into my dimension table I need to retrieve the latest record for that dimension (where SCD end date is null), how can I incorporate that rule while using DB_LOOKUP ?

For example I am doing DB_LOOKUP on JOBID to retrieve job_guid(surrogate key)to load my Fact table from my junk dimension d_umms_job_detail. in that dimension i will have several rows for that jobid but i need to retrieve the guid for the most recent record (where enddate is null)

DB_LOOKUP(d_umms_job_detail, JOBID, JOB_ID, UMMS_JOB_DET_GUID)

thanks for your help.

Regards
VS4

This message has been edited. Last edited by: Tamra,

That's the problem with NULL. You can't look for it.

Although the default value for the end date for active records is NULL (it seemed like a good idea at the time) you can override it by setting a value for the SCD variable &&CM__SCDEADT as a date far in the future, say 29991231 in the Variables object in the process flow. Then create a source transformation for a field with that value. Now you can use that as an additional field in your DB_LOOKUP.

Alternatively you could add an Active Flag to your dimension table. Then create a transformation for a field with value 1, and use that as the additional field in your DB_LOOKUP.