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

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. Re: Rewrite the Below select Query or try to  Get the Needed O/P
Solution:-
``````select a.name, decode( (select count(1) from do_not_call where dont_call = a.phone1), 0, '0', '1') ||
decode( (select count(1) from do_not_call where dont_call = a.phone2), 0, '0', '1') ||
decode( (select count(1) from do_not_call where dont_call = a.phone3), 0, '0', '1') bitcode
from customer a

NAME       BITCODE
---------- -------
a          010
b          000   ``````
• ###### 2. Re: Rewrite the Below select Query or try to  Get the Needed O/P
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;
You can also use CASE expression in Purvesh Solution.
• ###### 3. Re: Rewrite the Below select Query or try to  Get the Needed O/P
There's no need to count rows I think.
A simple test of existence should be fine enough, using EXISTS() or ANY() - as OP's first approach.

We could also do it with a single access to DO_NOT_CALL but is it any better?
``````SQL> with unpivot_phone as (
2    select name
3         , phonetype
4         , phone
5    from customer
6    unpivot include nulls (
7     phone for phonetype in (phone1, phone2, phone3)
8    )
9  )
10  select t.name
11       , listagg(nvl2(dnc.dont_call, '1', '0'))
12                within group (order by t.phonetype) as bitwise
13  from unpivot_phone t
14       left outer join do_not_call dnc on dnc.dont_call = t.phone
15  group by t.name
16  ;

NAME       BITWISE
---------- --------------------------------------------------------------------------------
a          010
b          000
``````
• ###### 4. Re: Rewrite the Below select Query or try to  Get the Needed O/P
Solution with Pivoting and Aggregation is exactly what I was trying to test. But my ineptitude with the Pivot/Un-pivot functions could not allow me to reach the Solution.

Using Decode the Exists/Any wasn't executing correctly, perhaps I did not try it that well, hence I used Count. But yes, Counting was needless :)
• ###### 5. Re: Rewrite the Below select Query or try to  Get the Needed O/P
Hi Odie 63 ,

your query is not working getting error message like

ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
Error at Line: 28 Column: 14

and also i want to know we can use like

<script>
unpivot include nulls (
7 phone for phonetype in (phone1, phone2, phone3)
<script>

• ###### 6. Re: Rewrite the Below select Query or try to  Get the Needed O/P
Are you working on Oracle 11g?

Can you post the results from
``select * from v\$version;``
• ###### 7. Re: Rewrite the Below select Query or try to  Get the Needed O/P
Yes!!! Thank You.
• ###### 8. Re: Rewrite the Below select Query or try to  Get the Needed O/P
>
Create table customer(name varchar2(10),phone1 number (10),phone2 number(10),phone3 number(10),bitwisephone number(10));
. . .
Actually it is not existing ,so set the bit for phone1 as 'o'.like wise check for phone2
>
Why are you denormalizing the data like that? Everytime the do not call table changes the data in the customer table will be out of sync.

Tell us what you are trying to accomplish so we can help you use the best data model and process to do it.
• ###### 9. Re: Rewrite the Below select Query or try to  Get the Needed O/P
Thank YOU All !!

I have one more doubt..

Performance wise which one is best, CASE or DECODE (Is there any difference)?

Thankz for Your support.

Lovz,
Vijayrajaram.
• ###### 10. Re: Rewrite the Below select Query or try to  Get the Needed O/P
Vijayrajaram wrote:
Thank YOU All !!

I have one more doubt..

Performance wise which one is best, CASE or DECODE (Is there any difference)?
Are you unwilling or incapable to simply benchmark both SQL YOURSELF?
• ###### 11. Re: Rewrite the Below select Query or try to  Get the Needed O/P
Yes, I'm using Oracle 10G.
• ###### 12. Re: Rewrite the Below select Query or try to  Get the Needed O/P
Vijayrajaram wrote:
Yes, I'm using Oracle 10G.
10G is NOT a version; but only a marketing label.
Both V10.1 & V10.2 are unsupported now!
• ###### 13. Re: Rewrite the Below select Query or try to  Get the Needed O/P
Oracle Database 10g Release 2 (10.2.0.4.0) .
• ###### 14. Re: Rewrite the Below select Query or try to  Get the Needed O/P
For you, SQL and PL/SQL FAQ is a must read.

Unpivot and LISTAGG would not work on 10g. An alternative to LISTAGG is Oracle String Aggregation. Alternative to Unpivot is Pivot and Unpivot in Oracle. You will have to modify the examples provided to suite your requirements.

Regards,
P.
1 2 Previous Next