5 Replies Latest reply: Jan 1, 2013 6:25 PM by 965772 RSS

    about case when and the sql clause

    965772
      Hi:
      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)
      end

      Thanks a lot.
        • 1. Re: about case when and the sql clause
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: about case when and the sql clause
            Purvesh K
            962769 wrote:
            Hi:
            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)
            end

            Thanks a lot.
            Your question isn't very clear. But below is the statement as I make a meaning out of it.
             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 '%'||NVL(:name, b.name)||'%'
             ORDER BY t.ChineseName
            If you want to use case, then:
             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 '%'|| case when b.name is not null then :name else b.name end ||'%'
             ORDER BY t.ChineseName
            If this is not what you are looking for then, read {message:id=9360002} and post the relevant details with an example (Create Table script, Sample Data creation script and the Expected output of the sample data).
            • 3. Re: about case when and the sql clause
              965772
              The main problem is the table is cross form, and the like clause will filter the
              data without name. Basically, it should show the empty columns, however, it not.
              so when I test the sql, I fhound Like '%'|| b.name ||'%' will not get the data when the data in original table dbtest1
              has no corresponding id to dbtest. That will make the LEFT OUTER JOIN seems useless.

              Thanks
              • 4. Re: about case when and the sql clause
                jeneesh
                962769 wrote:
                The main problem is the table is cross form, and the like clause will filter the
                data without name. Basically, it should show the empty columns, however, it not.
                so when I test the sql, I fhound Like '%'|| b.name ||'%' will not get the data when the data in original table dbtest1
                has no corresponding id to dbtest. That will make the LEFT OUTER JOIN seems useless.

                Thanks
                WHERE t.ChineseName like '%'||:ChineseName||'%' 
                AND  (
                        b.name like '%'||:name||'%'
                       or b.empno is null
                    )
                • 5. Re: about case when and the sql clause
                  965772
                  Thanks a lot