OraFAQ Forum: SQL & PL/SQL » Complicate Query

Home » SQL & PL/SQL » SQL & PL/SQL » Complicate Query (Oracle SQL )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Complicate Query [message #630256]

Tue, 23 December 2014 17:27

raajes_n
Messages: 33
Registered: April 2005
Location: India

Member

Hi All,

Am having one typical requirement as follows

CustId TELN_Type Primary_Code SubscriberNo
100 Home 44 26425218
100 Mobile 1234567890
100 Emergency 44 56565656
101 Home
101 Mobile 92929292922
102 Emergency 44 32323232

Am having the above table structure with data
My task is I need to check
1. If user is having home, Mobile, Emergency (all 3 not null)then I need to pick Primary_Code and SubscriberNo for Home and Mobile alone
2. If user is not having home telephone then I need to check for Mobile then I need to pick respective Primary_Code and SubscriberNo
3. If user is not having both Home and Mobile, then I need to pick Emergency

My output should be

CustId TelnType1 PrimaryCode1 SubscriberNo1 TelnType2 PrimaryCode2 SubscriberNo2
100 Home 44 26425218 Mobile 1234567890
101 Mobile 92929292922
102 Emergency 44 32323232

I've included the above requirement in Excel format also.

Need your help. Thanks in Advance

Attachment: Screenshot - 24_12_2014 , 4_54_17 AM.png
(Size: 148.23KB, Downloaded 749 times)

[Updated on: Wed, 24 December 2014 06:54] by Moderator

Report message to a moderator

Re: Complicate Query [message #630279 is a reply to message #630256]

Wed, 24 December 2014 01:34

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

No one will try anything to help you with your current post.

Report message to a moderator

Re: Complicate Query [message #630291 is a reply to message #630256]

Wed, 24 December 2014 03:52

raajes_n
Messages: 33
Registered: April 2005
Location: India

Member

Sorry for that inconvenience caused by me

-- Create table
create table
(
  id           number not null,
  custid       number not null,
  telntype     varchar2(50),
  primarycode  varchar2(10),
  subscriberno varchar2(25)
)
;
-- Create/Recreate primary, unique and foreign key constraints 
alter table 
  add constraint ID primary key (ID);

Am having the above table structure with data
My task is I need to check
1. If user is having home, Mobile, Emergency (all 3 not null)then I need to pick Primary_Code and SubscriberNo for Home and Mobile alone
2. If user is not having home telephone then I need to check for Mobile then I need to pick respective Primary_Code and SubscriberNo
3. If user is not having both Home and Mobile, then I need to pick Emergency

I need the output similar to the screen attached

Attachment: Screenshot+-+24_12_2014+%2C+4_54_17+AM.png
(Size: 148.23KB, Downloaded 689 times)

Report message to a moderator

Re: Complicate Query [message #630295 is a reply to message #630291]

Wed, 24 December 2014 04:27

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Data are missing.
Screenshots are useless, I don't look at them.
Post ALL inline and formatted.

Report message to a moderator

Re: Complicate Query [message #630298 is a reply to message #630295]

Wed, 24 December 2014 04:41

raajes_n
Messages: 33
Registered: April 2005
Location: India

Member


insert into custinfo values(1,100,'Home','044','22222222');
insert into custinfo values(2,100,'Mobile','','1234567890');
insert into custinfo values(3,100,'Emergency','044','2222222222');
insert into custinfo values(4,101,'Home','','');
insert into custinfo values(5,101,'Mobile','','0987654321');
insert into custinfo values(6,102,'Emergency','092','4444444444');

Report message to a moderator

Re: Complicate Query [message #630299 is a reply to message #630298]

Wed, 24 December 2014 04:43

raajes_n
Messages: 33
Registered: April 2005
Location: India

Member

Sorry Michael

create table CUSTINFO
(
sid NUMBER not null,
custid NUMBER,
telntype VARCHAR2(10),
primarycode VARCHAR2(5),
subscriberno VARCHAR2(25)
)

insert into custinfo values(1,100,'Home','044','22222222');
insert into custinfo values(2,100,'Mobile','','1234567890');
insert into custinfo values(3,100,'Emergency','044','2222222222');
insert into custinfo values(4,101,'Home','','');
insert into custinfo values(5,101,'Mobile','','0987654321');
insert into custinfo values(6,102,'Emergency','092','4444444444');

Report message to a moderator

Re: Complicate Query [message #630301 is a reply to message #630299]

Wed, 24 December 2014 04:51

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So what should be the result for these data?

Report message to a moderator

Re: Complicate Query [message #630302 is a reply to message #630301]

Wed, 24 December 2014 04:57

raajes_n
Messages: 33
Registered: April 2005
Location: India

Member

Thanks Michel for your immediate response, My output should be
as attached in the screenshot with yellow highlights.

Is this possible Michel, because am very very new to oracle.

Attachment: Screenshot+-+24_12_2014+%2C+4_54_17+AM.png
(Size: 133.49KB, Downloaded 694 times)

Report message to a moderator

Re: Complicate Query [message #630305 is a reply to message #630302]

Wed, 24 December 2014 05:13

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Once again, I don't look at screenshots.
SQL does not return a screen shot, SQL returns lines and columns, so put the result you want in this way.

Report message to a moderator

Re: Complicate Query [message #630310 is a reply to message #630305]

Wed, 24 December 2014 05:42

raajes_n
Messages: 33
Registered: April 2005
Location: India

Member

Hi Michel

Hope this will help you


CustId	TelnType1	PrimaryCode1	SubscriberNo1	TelnType2	PrimaryCode2	SubscriberNo2
100	Home	        44	        22222222	Mobile		                1234567890
101	Mobile		                987654321			
102	Emergency	92	        4444444444

Report message to a moderator

Re: Complicate Query [message #630314 is a reply to message #630310]

Wed, 24 December 2014 06:51

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> with 
  2    data as (
  3      select c.*,
  4             row_number() over 
  5               (partition by c.custid  
  6                order by decode(telntype, 'Emergency',1, 'Mobile',2, 'Home',3, 4)) rk
  7      from CUSTINFO c
  8    )
  9  select custid,
 10         max(decode(rk, 1, telntype)) telntype1,
 11         max(decode(rk, 1, primarycode)) primarycode1,
 12         max(decode(rk, 1, subscriberno)) subscriberno1,
 13         max(decode(rk, 2, telntype)) telntype2,
 14         max(decode(rk, 2, primarycode)) primarycode2,
 15         max(decode(rk, 2, subscriberno)) subscriberno2,
 16         max(decode(rk, 3, telntype)) telntype3,
 17         max(decode(rk, 3, primarycode)) primarycode3,
 18         max(decode(rk, 3, subscriberno)) subscriberno3
 19  from data
 20  group by custid
 21  order by custid
 22  /
    CUSTID TELNTYPE1  PRIMA SUBSCRIBERNO1 TELNTYPE2  PRIMA SUBSCRIBERNO2 TELNTYPE3  PRIMA SUBSCRIBERNO3
---------- ---------- ----- ------------- ---------- ----- ------------- ---------- ----- -------------
       100 Emergency  044   2222222222    Mobile           1234567890    Home       044   22222222
       101 Mobile           0987654321    Home
       102 Emergency  092   4444444444

3 rows selected.

[Updated on: Wed, 24 December 2014 06:53]

Report message to a moderator

Re: Complicate Query [message #630318 is a reply to message #630314]

Wed, 24 December 2014 07:04

raajes_n
Messages: 33
Registered: April 2005
Location: India

Member

Really Really you are genius...you helped me...am grateful to you

Report message to a moderator

Re: Complicate Query [message #630319 is a reply to message #630291]

Wed, 24 December 2014 07:16

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

raajes_n wrote on Wed, 24 December 2014 03:52

Sorry for that inconvenience caused by me

-- Create table
create table
(
  id           number not null,
  custid       number not null,
  telntype     varchar2(50),
  primarycode  varchar2(10),
  subscriberno varchar2(25)
)
;

As an aside from you current problem, I'd like to point up a problem with your table design. What is to prevent me from entering a value of 'this is fubar' for column TELNTYPE?

Think about alternatives that would prevent anything but valid values. And think about the amount of maintenance required by those alternatives when new valid values are created. There are a couple of alternatives I can think of right off the top of my head, one far better than the other.

Report message to a moderator

Re: Complicate Query [message #630320 is a reply to message #630318]

Wed, 24 December 2014 07:18

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

For your next questions, you now know what you have to do to get a faster answer. Wink

Report message to a moderator

Previous Topic:	How to write stored function to return column name?
Next Topic:	ASCII in oracle 11g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 15:00:45 CDT 2024