We are applying a logic currently using sparse lookup which is causing performance issue, would like to check if it can be implemented in any other way..
my source record would be like 98456728 (calling number)
corresponding records n lookup table
num_pfx svc_usg_refr_id
9 aaa
98 abb
567 ccc
9845 xxx
In this case i would do a pattern match , the id for which pattern matches most should be picked up ..so it would be 9845 ..and value would be xxx
Here the sql we had used for sparse lookup
select svc_usg_refr_id from
(
select svc_usg_refr_id from #PSET_DB_ATOMIC.$DB_AT_SCHEMA#.svc_usg_refr where
substr(orchestrate.CALLED_CALLING_NUMBER,1,length(num_pfx)) = num_pfx
and UPPER(SVC_USG_CGY_NM)=UPPER('NationalNumber') AND (DEL_F='C' OR DEL_F='I') order by num_pfx desc
) b
FETCH first 1 rows only;
here
Pattern matching in datastage join stage
Moderators: chulett, rschirm, roy