Pattern matching in datastage join stage

[nishantrk]

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

[ssnegi]

After Lookup, hash partition and Sort num_pfx svc_usg_refr_id descending in remove duplicate stage.
duplicate to retain first.

[bgs_vb]

Hi ,

You can perform a dummy join between these 2 datsets(Create Dummy column in both inputs with static value as 1) and then in Txfm view you can match pattern using substr /index function .