Here's an Obscure One

I spent a couple of hours this afternoon tracking down and resolving a very obscure bug. See anything wrong with the following SQL statement?

select ;
    CONTACT1.COMPANY, ;
    CONTACT1.KEY4, ;
    CONTACT2.UJRCONSULT ;
  from CONTACT2 ;
    right outer join CONTACT1 ;
      on CONTACT2.ACCOUNTNO=CONTACT1.ACCOUNTNO ;
  where CONTACT1.KEY4 like '%JDM%' or ;
    CONTACT2.UJRCONSULT like '%JDM%'

Neither did I, yet this gives a "Function argument value, type, or count is invalid" error. Long story short, it turns out this is the set of conditions that make this fail:

• An OUTER JOIN
• A filter condition on the "other" table (e.g. the left table for a RIGHT OUTER JOIN); in this case, it's the second condition that causes the error
• The field in the filter is a Varchar
• One or more records have a null value in that field
• The filter uses the LIKE operator

Changing any one of these makes the error go away. In my case, I changed the filter condition to:

nvl(CONTACT2.UJRCONSULT, '') like '%JDM%'

and that fixed the problem.