sql - Joining Using LIKE operator in Neteeza

Question

I have a table like:

which I am joining to another table:

I want to get an output table that looks like this:

I have discovered that my version of Netezza doesn't support concat but supports || instead. I have tried joining the table using the LIKE operator as follows:

SELECT A.*, CASE WHEN A.GENERIC_NAME LIKE ('%'||B.O_DRUGS||'%') THEN B.DRUG_CLASS ELSE NULL END AS OTHER_DRUGS FROM DRUG1 AS A LEFT JOIN DRUG_LIST AS B ON A.GENERIC_NAME LIKE ('%'||O_DRUGS||'%');

The code works for exact matches but the fuzzy matching is still not working. Clearly, I'm not using the like operator correctly for this purpose. Any suggestions??

question from:https://stackoverflow.com/questions/66055466/joining-using-like-operator-in-neteeza

Answer 1

Your LIKE conditions looks backwards:

FROM DRUG1 D1 LEFT JOIN
     DRUG_LIST DL
      ON DL.O_DRUGS LIKE '%' || D1.GENERIC_NAME || '%'

Note: Table aliases such as a are generally meaningless. Use abbreviations of the table names.