Report query [message #670434] |
Wed, 04 July 2018 10:20 |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
Team,
We have below requirement for report query. please do the needful.
Create table Dist_details6
(
DISTRIBUTION_LIST_SYS_ID VARCHAR2(100),
distribution_list_name varchar2(100),
VENDOR_SYS_ID VARCHAR2(100),
VENDOR_NAME VARCHAR2(100),
CONTACT_NAME VARCHAR2(200)
)
;
Insert into Dist_details6 values ('1368133243966','Verizon team','1154','VERIZON NEW JERSEY','Adam Russell');
Insert into Dist_details6 values ('1368133243966','Verizon team','1354','VERIZON','Karla Page');
Insert into Dist_details6 values ('1368133243966','Verizon team','0000','VER','verizoncom1');
commit;
DISTRIBUTION_LIST_SYS_ID DISTRIBUTION_LIST_NAME VENDOR_SYS_ID VENDOR_NAME CONTACT_NAME
1368133243966 Verizon team 1154 VERIZON NEW JERSEY Adam Russell
1368133243966 Verizon team 1354 VERIZON Karla Page
1368133243966 Verizon team 0000 VER verizoncom1
The distribution name (distribution_list_name) is pointed to different vendors (VENDOR_NAME) and each vendor has different contact names (CONTACT_NAME).
Requirement: Get query result : For each distribution list, for each vendor names, get other contact names as well.
Expected result:
DISTRIBUTION_LIST_SYS_ID DISTRIBUTION_LIST_NAME VENDOR_SYS_ID VENDOR_NAME CONTACT_NAME
1368133243966 Verizon team 1154 VERIZON NEW JERSEY Karla Page
1368133243966 Verizon team 1154 VERIZON NEW JERSEY verizoncom1
1368133243966 Verizon team 1154 VERIZON NEW JERSEY Adam Russell
1368133243966 Verizon team 1354 VERIZON Adam Russell
1368133243966 Verizon team 1354 VERIZON verizoncom1
1368133243966 Verizon team 1354 VERIZON Karla Page
1368133243966 Verizon team 0000 VER Adam Russell
1368133243966 Verizon team 0000 VER Karla Page
1368133243966 Verizon team 0000 VER verizoncom1
Please advise.
Thanks,
SRK
[mod-edt: additional code tags added by bb]
[Updated on: Sat, 07 July 2018 19:52] by Moderator Report message to a moderator
|
|
|
|
Re: Report query [message #670436 is a reply to message #670435] |
Wed, 04 July 2018 11:00 |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
We are not getting expected result... The contact names are duplicated. Please advise.
DISTRIBUTION_LIST_SYS_ID DISTRIBUTION_LIST_NAME VENDOR_SYS_ID VENDOR_NAME CONTACT_NAME
1368133243966 Verizon team 1154 VERIZON NEW JERSEY Adam Russell
1368133243966 Verizon team 1154 VERIZON NEW JERSEY Adam Russell
1368133243966 Verizon team 1154 VERIZON NEW JERSEY Adam Russell
1368133243966 Verizon team 1354 VERIZON Karla Page
1368133243966 Verizon team 1354 VERIZON Karla Page
1368133243966 Verizon team 1354 VERIZON Karla Page
1368133243966 Verizon team 0000 VER verizoncom1
1368133243966 Verizon team 0000 VER verizoncom1
1368133243966 Verizon team 0000 VER verizoncom1
Thanks,
SRK
[mod-edit: code tags added by bb]
[Updated on: Sat, 07 July 2018 20:04] by Moderator Report message to a moderator
|
|
|
|
Re: Report query [message #670463 is a reply to message #670436] |
Thu, 05 July 2018 03:43 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Do you think I can see your PC from here to know what select you are actually running?
After 12 years on the forum you should know we can't do that.
|
|
|
Re: Report query [message #670472 is a reply to message #670434] |
Sat, 07 July 2018 20:06 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> column distribution_list_sys_id format a24
SCOTT@orcl_12.1.0.2.0> column distribution_list_name format a22
SCOTT@orcl_12.1.0.2.0> column vendor_sys_id format a13
SCOTT@orcl_12.1.0.2.0> column vendor_name format a18
SCOTT@orcl_12.1.0.2.0> column contact_name format a12
SCOTT@orcl_12.1.0.2.0> select * from dist_details6
2 /
DISTRIBUTION_LIST_SYS_ID DISTRIBUTION_LIST_NAME VENDOR_SYS_ID VENDOR_NAME CONTACT_NAME
------------------------ ---------------------- ------------- ------------------ ------------
1368133243966 Verizon team 1154 VERIZON NEW JERSEY Adam Russell
1368133243966 Verizon team 1354 VERIZON Karla Page
1368133243966 Verizon team 0000 VER verizoncom1
3 rows selected.
SCOTT@orcl_12.1.0.2.0> select t1.distribution_list_sys_id, t1.distribution_list_name, t1.vendor_sys_id, t1.vendor_name,
2 t2.contact_name
3 from dist_details6 t1, dist_details6 t2
4 where t1.distribution_list_sys_id = t2.distribution_list_sys_id
5 and t1.distribution_list_name = t2.distribution_list_name
6 order by distribution_list_sys_id, distribution_list_name, vendor_name desc, contact_name
7 /
DISTRIBUTION_LIST_SYS_ID DISTRIBUTION_LIST_NAME VENDOR_SYS_ID VENDOR_NAME CONTACT_NAME
------------------------ ---------------------- ------------- ------------------ ------------
1368133243966 Verizon team 1154 VERIZON NEW JERSEY Adam Russell
1368133243966 Verizon team 1154 VERIZON NEW JERSEY Karla Page
1368133243966 Verizon team 1154 VERIZON NEW JERSEY verizoncom1
1368133243966 Verizon team 1354 VERIZON Adam Russell
1368133243966 Verizon team 1354 VERIZON Karla Page
1368133243966 Verizon team 1354 VERIZON verizoncom1
1368133243966 Verizon team 0000 VER Adam Russell
1368133243966 Verizon team 0000 VER Karla Page
1368133243966 Verizon team 0000 VER verizoncom1
9 rows selected.
|
|
|
Re: Report query [message #670496 is a reply to message #670472] |
Mon, 09 July 2018 07:58 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
except no joining is necessary. This is a perfect example of using a Cartesian join.
SELECT T1.Distribution_list_sys_id,
T1.Distribution_list_name,
T1.Vendor_sys_id,
T1.Vendor_name,
T2.Contact_name
FROM Dist_details6 T1, Dist_details6 T2
ORDER BY Distribution_list_sys_id,
Distribution_list_name,
Vendor_name DESC,
Contact_name
The results are
DISTRIBUTION_LIST_SYS_ID DISTRIBUTION_LIST_NAME VENDOR_SYS_ID VENDOR_NAME CONTACT_NAME
------------------------ ---------------------- ------------- -------------------- ---------------
1368133243966 Verizon team 1154 VERIZON NEW JERSEY Adam Russell
1368133243966 Verizon team 1154 VERIZON NEW JERSEY Karla Page
1368133243966 Verizon team 1154 VERIZON NEW JERSEY verizoncom1
1368133243966 Verizon team 1354 VERIZON Adam Russell
1368133243966 Verizon team 1354 VERIZON Karla Page
1368133243966 Verizon team 1354 VERIZON verizoncom1
1368133243966 Verizon team 0000 VER Adam Russell
1368133243966 Verizon team 0000 VER Karla Page
1368133243966 Verizon team 0000 VER verizoncom1
|
|
|
Re: Report query [message #670497 is a reply to message #670496] |
Mon, 09 July 2018 08:18 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I'm pretty sure that this is a case of the OPs sample data set being overly limited.
They say this
Quote:
The distribution name (distribution_list_name) is pointed to different vendors (VENDOR_NAME) and each vendor has different contact names (CONTACT_NAME).
Which implies some form of self referential join rather than an actual cartesian.
|
|
|
Re: Report query [message #670498 is a reply to message #670463] |
Mon, 09 July 2018 08:22 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
cookiemonster wrote on Thu, 05 July 2018 04:43Do you think I can see your PC from here to know what select you are actually running?
After 12 years on the forum you should know we can't do that.
I'm sorry, he is just a lazy person with no right being an Oracle programmer. Can't describe problem, can't spend the effort to format posts, etc.
|
|
|
Re: Report query [message #670499 is a reply to message #670498] |
Mon, 09 July 2018 08:32 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I don't disagree, hence my comment before about not being able to see his PC.
I suspect I know what mistake he made as well, but I wasn't going to say anything till he posted the query he was using.
EDIT: didn't actually read what you quoted, which may make my reply seem a bit odd.
[Updated on: Mon, 09 July 2018 08:34] Report message to a moderator
|
|
|
Re: Report query [message #670504 is a reply to message #670499] |
Mon, 09 July 2018 12:38 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Heh, I may have quoted incorrectly, but I "think" I picked up the correct context. I was just quoting you to reiterate what you were saying.
|
|
|