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

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

    947624
      Hi..,

      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);

      Question:-
      --------------

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

      Regards,
      VijayRajaram.
        1 2 Previous Next