1 Reply Latest reply: Dec 27, 2012 7:35 AM by Jim Smith RSS

    about case when and the sql clause

      The following is my sql clause:

      SELECT t.*,b.name
      FROM dbtest1 t
      LEFT OUTER JOIN dbtest b ON t.NO = b.empno
      WHERE t.ChineseName like '%'||:ChineseName||'%' AND b.name like '%'||:name||'%'
      ORDER BY t.ChineseName

      The main problem is I hope to check the b.name if it is null it can be passed as NVL function, so I try using case when, but it not working.
      When b.name exist in where clause, the result columns will not include the data without b.name(or implies the b.name is NULL); And that
      make the result data not exactly right.
      is it possible to use case when to make the following snippet implemented:

      case when b.name is not NULL then b.name like '%'||:name||'%'
      else b.name = NULL(b.name=b.name seems not work to parsing null data)

      Thanks a lot.

      Edited by: 962769 on 2012/12/26 下午 6:50