1 2 Previous Next 15 Replies Latest reply on Jul 24, 2012 8:01 AM by Etbin

    Rewrite the Below select Query or try to  Get the Needed O/P


      My Sample data.,

      Create table customer(name varchar2(10),phone1 number (10),phone2 number(10),phone3 number(10),bitwisephone number(10));

      insert into customer values('a',23456,67890,null,12345);

      insert into customer values('b',67459,89760,null,37689);

      create table do_not_call(dont_call number(10));

      insert into do_not_call values(67890);

      insert into do_not_call values(37689);


      Customer'a' has phone1 value as 23456.check whether phone1 exists in do_not_call table.
      Actually it is not existing ,so set the bit for phone1 as 'o'.like wise check for phone2
      &phone3.after updating the bitwisephone for each customer should be like the below output.

      Needed O/P:-

      name bitwisephone

      a 010
      b 001

      For this Question ..i'm using "any" operator..like

      SELECT name,case when phone1=any(SELECT dont_call FROM do_not_call) then '1' else '0' end ||
      case when phone2=any(SELECT dont_call FROM do_not_call) then '1' else '0' end ||
      case when phone3=any(SELECT dont_call FROM do_not_call) then '1' else '0' end "Bitwise"
      FROM customer;

      Is there any other easiest way to get the needed O/P?

      Thank You!!

        1 2 Previous Next