2 Replies Latest reply on Feb 12, 2013 8:32 PM by Frank Kulash

    compare two columns

      ename      d_ename
      john            john
      mary            mary
      target table (emp_target)
      f_ename---should populate the value based on ename and d_ename. compare two columns if any of the column is null then take column which is not null. if both the columns are populated then take any of the column. it will have same values.

      Please help me how can i have value for f_ename in emp_target table.
      expected output
      compare two columns
      and take whatever is not null
        • 1. Re: compare two columns
          ranit B
          Like this???
          with xx as(
            select 'john' ename, 'john' d_ename from dual UNION ALL
            select NULL ename, 'chris' d_ename from dual UNION ALL
            select 'joe' ename, NULL d_ename from dual UNION ALL
            select 'mary' ename, 'mary' d_ename from dual 
            NVL(ename,d_ename) from xx;
          Output :
          NVL - substitutes a value when a NULL is encountered.
          <b>NVL Quick Link</b>
          • 2. Re: compare two columns
            Frank Kulash

            As Ranit show, you can used NVL, or the sometimes faster COALESCE function.

            If you're populating the emp_target table with values from the emp table, then you can use NVL (or COALESCE) in an INSERT statement, like this:
            INSERT INTO  emp_target (f_name)
            SELECT  COALESCE (ename, d_ename)
            FROM     emp

            I hope this answers your question.
            If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
            If you're asking about a DML statement, such as INSERT, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
            Explain, using specific examples, how you get those results from that data.
            Always say which version of Oracle you're using (e.g.,
            See the forum FAQ {message:id=9360002}