Custom SQL in Candidate Selection

dataservices data quality
1

Hi,

We have developed a real time job and exposed it as a webservice.
In this job, we have a match transform using the candidate selection option. Here, Create Custom SQL option seems to be faster than the Auto generate SQL option.Please confirm if this understanding is correct.
We are testing this from SOAP UI.

Description:
In this job, we are passing 1 address record as the xml input and checking if this input record exists in the Reference Table. If it exists,we say it as a MATCH else NO MATCH. For achieving this, we are using Match Transform. We are using combination of 2 characters ISO country code and first 4 digits of zip code as the break key. We have created this break key field in the reference table as well and added a normal index on this column. Now inside Match transform editor, we are using Break Group option, candidate selection and few Match criterias. The cache type is set to No Cache. When we are running this real time job manually from Designer,passing a xml input record which already exists in the ref table, then it works fine and returns as MATCH. Each time we execute, we get the same result. But when we call the same job form SOAP UI, first time, it says MATCH and second time it says NO MATCH for the same record.

Can you please help.I am attaching the settings of candidate selection option. Are all the settings correct in this?

Also this issue(2nd run from SOAP UI , incorrect result) is happening only with Custom SQL option.

It seems to work fine with Auto Generate SQL option but bit slow.
We are using DS-3.2 version and using Oracle datastore.

I really appreciate your help!

Thanks,
Ragini
Candidate Selection options.png

Shivbaba :us: (BOB member since 2010-06-01)

2

couple things about matching in general:

matched results will be much better using parsed fields.

if you have a match field contained within your break key, set as the primary match field… that field is already known to match as it defines your break groups.

If you have a postcode populated (which is driving your break groups), then your city and region should also be known to match. Working under the assumption that the postcode is in fact valid.

as far as why the candidate selector isnt working right? You should probably just bite the bullet and open a ticket. :nopity:

jlynn73 :us: (BOB member since 2009-10-27)

3

Hi Jlynn,

Thanks for your response. I raised a ticket with SAP and got it resolved. This is a known issue and fixed in SAP Data Services Fix Pack 12.2.3.5

Please refer below:
ADAPT01605936
A real-time job with candidate selection using customer SQL, gives an empty response from 2nd request onwards. This issue has been fixed in this release.

Also just wanted to share that this issue is present in BODS 4.0 version as well and fixed in 4.0 SP2 Patch 5.

Shivbaba :us: (BOB member since 2010-06-01)