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

    compare two columns

    choti
      source(emp)
      -----
      ename      d_ename
      --------------------------
      john            john
                        chris
      
      joe                    
      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
      
      f_name
      -----
      john   
      chris
      joe     
      mary
      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 
          )
          select 
            NVL(ename,d_ename) from xx;
          Output :
          john
          chris
          joe
          mary
          NVL - substitutes a value when a NULL is encountered.
          <b>NVL Quick Link</b>
          • 2. Re: compare two columns
            Frank Kulash
            Hi,

            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., 11.2.0.2.0).
            See the forum FAQ {message:id=9360002}