This discussion is archived
3 Replies Latest reply: Nov 25, 2012 7:36 PM by Frank Kulash RSS

How to compare two columns

751828 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points