How to use '%LIKE%' to join in Query transformation

[Former Member]

Hi Experts,

I need to implement the below join condition in Query transformation.

SQL:

SELECT A.COL1.B.COL3,B.COL4,B.COL7

FROM TABLE1 AS A, TABLE2 AS B

WHERE A.COL2 LIKE '%'+ B.COL2 +'%'

In Query transformation under expression, it is not allowing to add '%' (wild cards). It throws error message.

Also I have tried with lookup_ext function with operartor as ~.

Could you please suggest on this..

Thanks in advance !

[former_member187605 · Accepted solution]

The concatenation symbol in DS is not "+", but "||".

[Former Member · Accepted solution]

Hi Dirk,

Join Condition:

Error:

Please suggest

[former_member187605 · Accepted solution]

Oops .

But you can always use

     pushdown_sql('<datastore name>', 'OEM_PART_NUMBER like \'%\'+MATERIAL_NUMBER+\'%\'')

in the Where tab.