CODEWITHSUNDEEP
sqlnetezza
Prajwal Mani Pradhan
Prajwal Mani Pradhan

Reputation: 397

Joining Using LIKE operator in Neteeza

I have a table like: enter image description here

which I am joining to another table: enter image description here

I want to get an output table that looks like this: enter image description here

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??

Upvotes: 0

Views: 397

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270331

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.

Upvotes: 3

Related Questions