Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011
    Posts
    1

    Unanswered: compare the values from 2 columns

    I have an query that will display the result as following table:

    ID Num_1 Num_2
    -- ----- -----
    A1 0000 0100
    A2 1000 1000
    A3 0010 0000

    I want to compare the digits in Num_1 one by one with the digits in Num_2 based on the ID.
    If the digits in Num_1 is "0" and digits in Num_2 is "1", I will only display A1 as my result and the rest I will ignore it.

    Can anyone guide how to do this...

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can anyone guide how to do this...

    post results from command below

    SQL> DESC <table_name>

    solution depends upon datatype of NUM_1 & NUM_2
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here you are:
    Code:
    SQL> select * from test;
    
    ID NUM_ NUM_
    -- ---- ----
    A1 0000 0100
    A2 1000 1000
    A3 0010 0000
    
    SQL> select t.id
      2  from test t,
      3       table(cast(multiset(select level from dual
      4                           connect by level <= length(t.num_1)
      5                          ) as sys.odcinumberlist)) c
      6  where substr(t.num_1, c.column_value, 1) = '0'
      7    and substr(t.num_2, c.column_value, 1) = '1';
    
    ID
    --
    A1
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •