Oracle SQL use Coalesce

Question

I have this query that works in Oracle but I want to convert it to use Coalesce because of some problems I'm having with Visual Studio.

SELECT * 
FROM a Left Join b on b.institution_code=a.institution_code
WHERE 
      (upper(a.Login_Name)=UPPER('%' || :Login_Name || '%') OR :Login_Name IS NULL)
  AND (upper(a.Display_Name) Like  UPPER('%' || :Display_Name || '%')   OR :Display_Name IS NULL) 
  AND (upper(a.Email_Address)=UPPER(:Email_Address) OR :Email_Address IS NULL) 
  AND ((a.institution_code=:institution_code) OR :institution_code IS NULL)  
  AND (upper(b.institution_desc) Like  UPPER('%' || :institution_desc || '%')   OR :institution_desc IS NULL)

This works

WHERE
Upper(a.Display_Name) LIKE Upper('%' || COALESCE(:Display_Name,a.Display_Name) || '%')
AND upper(a.Login_Name)=Upper(COALESCE(:Login_Name,a.Login_Name))    
AND upper(a.Email_Address)=Upper(COALESCE(:Email_Address,a.Email_Address))

but when I try to convert the institution_code and institution_desc field to use coalesce i don't get any results from the queries.

when i add these lines there are no results

    AND a.institution_code=COALESCE(:institution_code,a.institution_code)
  AND (Upper(b.institution_desc) LIKE Upper('%' || COALESCE(:institution_desc,b.institution_desc) || '%'))

Answer 1

I suspect the problem is the 2nd line (b.institution_desc). Specifically, you have the possibility for a NULL LIKE NULL situation, and NULL LIKE NULL returns false.

Try it like this:

AND Upper(b.institution_desc) LIKE '%' || Upper(COALESCE(:institution_desc,b.institution_desc,'')) || '%'

Answer 2

WHERE (UPPER(a.Login_Name) = UPPER('%' || :Login_Name || '%') OR :Login_Name IS NULL)

...should be:

WHERE (:Login_Name IS NULL OR UPPER(a.Login_Name) = UPPER('%' || :Login_Name || '%'))

You want to check the bind variable FIRST - otherwise you're doing data comparison before knowing that you want everything anyways. You're getting the worst possible performance out of the query.

Next, if you're going to run UPPER on numerous columns - use Subquery Factoring to have it performed first:

WITH upper_a AS (
SELECT a.pk,
       UPPER(a.login_name) 'login_name',
       UPPER(a.display_name) 'display_name',
       UPPER(a.email_address) 'email_address',
       UPPER(b.institution_desc) 'institution_desc'
  FROM a)
SELECT *
  FROM A a
  JOIN upper_a ua ON ua.pk = a.pk
 WHERE :Login_Name IS NULL OR ua.login_name = v_login_name

Oracle doesn't do WHERE (parmeter IS NULL OR t.col = parameter) well, and you get nothing by using bind variables for parameters with optional values. What you really want to use is CONTEXT variables (available since 9i).

Answer 3

AND ((a.institution_code=:institution_code) OR :institution_code IS NULL)

is not equivalent to

AND a.institution_code=COALESCE(:institution_code,a.institution_code)

IF a.institution_code IS NULL and :institution_code IS NULL, then the first example will be true (because the second part of the OR is true), but the second will not (because NULL=NULL is not true).