Home » SQL & PL/SQL » SQL & PL/SQL » Report query (Oracle 12CR2)
Report query [message #670434] Wed, 04 July 2018 10:20 Go to next message
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 #670435 is a reply to message #670434] Wed, 04 July 2018 10:33 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Join the table to itself on DISTRIBUTION_LIST_NAME
Re: Report query [message #670436 is a reply to message #670435] Wed, 04 July 2018 11:00 Go to previous messageGo to next message
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 #670437 is a reply to message #670436] Wed, 04 July 2018 11:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
srinivas.k2005 wrote on Wed, 04 July 2018 09:00
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
we can't say what exactly you do wrong since you decided to NOT show us the SQL producing the incorrect results.

My car won't go.
Tell me how to make my car go.
Re: Report query [message #670463 is a reply to message #670436] Thu, 05 July 2018 03:43 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Thu, 05 July 2018 04:43
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.
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous message
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.
Previous Topic: type of constraints
Next Topic: How can bulk insert into the parent , child tables
Goto Forum:
  


Current Time: Thu Mar 28 16:41:43 CDT 2024