3 Replies Latest reply: Nov 25, 2012 9:36 PM by Frank Kulash RSS

    How to compare two columns

    751828
      iam having two tables

      desc temp_t_person
      Name Null Type
      --------- ---- --------------
      SURNAME VARCHAR2(4000)
      FIRSTNAME VARCHAR2(4000)
      DOB VARCHAR2(100)



      desc t_person
      Name Null Type
      ------------------------- -------- --------------

      SURNAME VARCHAR2(60)
      FIRSTNAME VARCHAR2(60)
      EMPLOYEE_NO VARCHAR2(10)
      DATEOFBIRTH DATE

      iam having data in excel sheet and created a table temp_t_person and dump that data into that table.

      excel sheet data is a below


      Aitken     Keith Stuart     3/10/1978
      Allan     Ross     3/3/1976
      Allen     Michael     12/7/1969
      Anderson     Paul     10/5/1977
      Anderson     Steven     3/12/1987
      Balakrishnan     Sateesh     10/10/1981
      Barclay     Gordon     15/6/1969
      Barnes     Stuart     22/11/1987
      Beagley     Steven     1/1/1965
      Beaton     Neil     1/8/1965
      Belboda     Karen     28/6/1957
      Bell     Joseph     3/11/1990
      Berry     Neal     18/9/1976
      Boath     Derek     16/4/1980
      Bourke     Howard     23/2/1963
      Bowie     Alexander     8/3/1967
      Boyd     Keith     11/12/1962
      Briggs     Mark Richard     23/8/1970
      Brook     Douglas     19/7/1979
      Brown     Peter     25/12/1956
      Buchan     Allan     18/5/1984
      Buchan     James     17/3/1981
      Buchan     Martin     28/7/1980
      Bujniewicz     Dominic     21/6/1983
      Cairns     Stephen     30/9/1969
      Campbell     Martin     7/8/1971
      Capes     Rian     26/5/1982
      Carbaugh     Christopher     26/9/1966
      Clark     Darren     5/11/1979
      Close.     Warren     15/9/1968
      Cooper     Christopher     2/3/1981
      Cormack     Callan     10/12/1985
      Cormack     Jonathan     22/6/1989
      Coull     Owen     2/7/1991
      Coyne     Richard     13/3/1963


      iam having other table t_person and now i just want to compare these temp_t_person data exists in t_person table or not.

      select SURNAME ,
      FIRSTNAME ,
      Dateofbirth
      from t_person


      Mallett     Robert     
      Wilson     Brian     28-DEC-61
      Kirkwood     Simon     30-OCT-73
      Todd     Paul     03-JUN-61
      Liebnitz     Priscilla     
      Gaimster     Martin     
      Finnie     Alan Bruce     13-NOV-66
      Scott     Mark     01-MAY-67
      Murray     Gary     
      Bagnall     Pete     
      Wilding     Peter Leonard     16-SEP-58




      You can see the date format for both excelsheet and output for t_persons, because of this cannot able to compare.

      Edited by: user9093700 on Nov 26, 2012 11:29 AM

      Edited by: user9093700 on Nov 26, 2012 12:09 PM
        • 1. Re: How to compare two columns
          rp0428
          >
          iam having two tables

          t_person --- having one column Dateofbirth(date)

          temp_t_person--- having a column DOB(varchar2(100))

          How to compare this two columns, i tried in as below can anyone correct me..

          (select trim(upper(surname)), trim(upper(firstname)), dob from TEMP_T_PERSON intersect

          select trim(upper(surname)), trim(upper(firstname)), to_char(dateofbirth,'dd/mm/yyyy') from T_Person where company_id = 207930)
          >
          Why don't you edit your post and start over.

          You said that t_person only has one column but then you show code that uses 'surname', 'firstname' and 'company_id. Does the table have one column or not?

          Post the actual DDL for the two tables and tell us, in English, what you are trying to do. Provide some sample data for each table and show us what you want the result to be. Just saying you want to 'compare this two columns' isn't enough.

          1. What are you trying to find out?
          2. Is it which records in table1 have the same dates as table2?
          3. Or which records in table1 have different dates than table2?
          4. What if there is more than one record in the table for the same name and id. What do you want to do with these?
          • 2. Re: How to compare two columns
            sb92075
            user9093700 wrote:
            iam having two tables

            t_person --- having one column Dateofbirth(date)

            temp_t_person--- having a column DOB(varchar2(100))
            You should NEVER store date (DOB) in VARCHAR2 datatype
            • 3. Re: How to compare two columns
              Frank Kulash
              Hi,
              user9093700 wrote:
              iam having two tables

              t_person --- having one column Dateofbirth(date)

              temp_t_person--- having a column DOB(varchar2(100))
              Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements) for all the tables involved, so the people who want to help you can re-create the problem and test their ideas. Also post the results you want from that data, and an explanation of how you get those results from that data.
              Explain, using specific examples, how you get those results from that data.
              lways say what version of Oracle you're using (e.g. 11.2.0.2.0).
              See the forum FAQ {message:id=9360002}
              How to compare this two columns, i tried in as below can anyone correct me..

              (select trim(upper(surname)), trim(upper(firstname)), dob from TEMP_T_PERSON intersect

              select trim(upper(surname)), trim(upper(firstname)), to_char(dateofbirth,'dd/mm/yyyy') from T_Person where company_id = 207930)
              That looks right, assuming the format of temp_t_person.dob is 'dd/mm/yyyy'. But that's only 10 characters, and dob can be 100 characters long. What is in postions 11 through 100? If you want to ignore those extra characters, then maybe you need to use
              SUBSTR (dob, 1, 10)
              in your query instead of dob.

              Post CREATE TABLE and INSERT statements for some sample data (that is not producing the output you want), and the resuls you want from that data.

              This problem shows one of the reasons why storing date information in a VARCHAR2 column is such a bad idea.