compare the values from 2 columns

mytan10 · #1 (**permalink**) 12-07-11, 10:01

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...

anacedent · #2 (**permalink**) 12-07-11, 10:03

>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

Littlefoot · #3 (**permalink**) 12-07-11, 16:01

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>

Internet Services

Web Development

Digital Marketing

Consumer Tech