pattern matching job

[prasson_ibm]

Hi,

I have requirement to search the sub string in the reference data.I have to check the existence of substring in the lookup master table.If it is present then process the record otherwise reject.

For this i have created the "Sparse Lookup" job in which i have source and reference has ODBC Connector stage.

I made reference as "sparse" and written user defined sql as

Code: Select all

SELECT COL1 FROM DB2ADMIN.TEST WHERE ORCHESTRATE.COL1 = COL1

For this query job is working fine but its matching the exact string in the reference table.

But when i changed this query to

Code: Select all

SELECT COL1 FROM DB2ADMIN.TEST WHERE ORCHESTRATE.COL1 like '%COL1%'

Then all rows are rejected.

Can anyone help me to resolve this issue.

Thanks
Prasoon

[ankursaxena.2003]

Try to use the following function:

Index(%string%,%substring%,%occurrence%)

%String% ---> Original String.
%substring% ---> patern you are searching for.
%occurrence% ---> you can hard code it as 1.

Use the above function in SV1. And later put a constraint that SV1>0 then process the records, else reject.

You will get SV1=0, if it is not able to find substring in the String, else it will have some value greater than 0.

[jwiles]

The records were rejected because your sql was searching for the string 'COL1' within the orchestrate column. Try the following sql logic instead:

Code: Select all

SELECT COL1 FROM DB2ADMIN.TEST WHERE LOCATE_IN_STRING(ORCHESTRATE.COL1,COL1,1) > 0

Regards,

[prasson_ibm]

I am getting error as
Unexpected procedure reference found.

[ankursaxena.2003]

Did you try my approach?

[chulett]

Your approach doesn't look applicable to me. It's not about pattern matching between two known values, they need to do a lookup using a substring match.

[jwiles]

Is there more to the error message? I suspect there is (SQLCODE, SQLSTATE, etc.)...just copy and paste the entire message.

What was the select statement you used? Copy and paste it as well, don't retype it.

Regards,