LIKE Operation

[Gopinath]

Hi,

In one of my existing Jobs, we are using a SQL query to fetch records from different tables. One of the Join condition used is LIKE option between two columns from two different tables. Now i have to extract the records simply using the table and need to handle the logic & join function in Datastage.
Ex:
table1.column1 LIKE table2.column%
ABC AB%
ABD ABC%

Iam just appending % to the table2 column in the extract query, so accordingly i should get the required join output. How to handle this LIKE option in Data stage. Please help me.
Thanks.

[ray.wurlod]

Sparse lookup with LIKE condition in user-defined SQL in the stage providing rows to the reference input link.

[Gopinath]

Hi Ray,

I have to handle this LIKE operation between two different datasets. i.e) from the extract Query i will handle some logical functions in Data stage and load into a dataset. Then from two different streams(Dataset1 & Dataset2) i need to handle this LIKE operation.
Can u pls guide me.
Thanks.

[Sainath.Srinivasan]

Does the reference INTO WHICH you are doing the LIKE come from any database table? If yes, you can push LIKE in the stage linking the table rather than using the dataset which is the result of its extraction.

Alternatively you can write a buildop to look for pattern.

Btw, how big is this LIKE table? The size of this table can give you more options.

[chulett]

In one of my existing Jobs, we are using a SQL query to fetch records from different tables. One of the Join condition used is LIKE option between two columns from two different tables.

That's what they are doing now. For some reason there's a need to change that.

[Sainath.Srinivasan]

If it is a join with more columns, you can let it pass through and do an index on the LIKE column after the join.

So add a transformer after the join to constraint rows which satisfy the index.

If the match is complicated - something like '%A%B%C%', then you can write your own function.