SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Function to display full name

01808a64-5eac-45dd-9da2-d9e9bd4b3b46May 19 2017 — edited May 19 2017

Hello,

I'm creating a report where I'm joining 2 tables, Table_1 and Table_2 (very simple). Here are tables with samples data:

Table_1
emp_id	manager_id	task_desc	task_dt
100	10012	ABCDEF	JAN-01-2010
101	null	FDFDFDK	JAN-01-2011
102	null	DFDAFDA	JAN-01-2012
103	10015	FDADDFGG	JAN-01-2013
104	null	DFDAFDA	JAN-01-2014
105	null	FDFDFDK	JAN-01-2015
106	10018	DFDAFDA	JAN-01-2016
107	null	DFDAFDA	JAN-01-2017
108	null	DFA	JAN-01-2018
109	null	FGG	JAN-01-2019

Table_2
emp_id	emp_name	desc	location	date
100	AJ SMITH	ABCDEF	UK	March-12-1970
101	Brad Jordan	FDFDFDK	USA	March-12-1971
102	Sam Mendez	DFDAFDA	USA	March-12-1972
103	Johny Jones	FDADDFGG	USA	March-12-1973
104	Scott Edwin	DFDAFDA	Canada	March-12-1974
105	James Jarule	FDFDFDK	USA	March-12-1975
106	Jackson Campbel	DFDAFDA	New Zealand	March-12-1976
107	Zack Brandon	DFDAFDA	USA	March-12-1977
108	Mary Casada	DFA	USA	March-12-1978
109	Monica Fort	FGG	Australia	March-12-1979
110	Brenda Levin	ABCDEF	Africa	March-12-1980
111	Phan Xiao	FDFDFDK	Vietnam	March-12-1981
112	Gopi Chand	DFDAFDA	India	March-12-1982
113	Mauro Mauricio	FDADDFGG	Spain	March-12-1983

I'd like to create a report which should look like this: I think 'm looking for a function which will grab manager_id then look for emp_id then display emp_name, but if manager_id is null then display null.

Sample expected report:

emp_id	manager name	task_desc	location	date
100	AJ SMITH	ABCDEF	UK	March-12-1970
101	null	FDFDFDK	USA	March-12-1971
102	null	DFDAFDA	USA	March-12-1972
103	Johny Jones	FDADDFGG	USA	March-12-1973
104	null	DFDAFDA	Canada	March-12-1974
105	null	FDFDFDK	USA	March-12-1975
106	Jackson Campbel	DFDAFDA	New Zealand	March-12-1976
107	null	DFDAFDA	USA	March-12-1977
108	null	DFA	USA	March-12-1978
109	null	FGG	Australia	March-12-1979

don't have that much knowledge in function, if someone can help me figure out on this that will be great.

Thanks

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 16 2017

Added on May 19 2017

3 comments

388 views