Home » SQL & PL/SQL » SQL & PL/SQL » retrieval of employee name
retrieval of employee name [message #284598] Fri, 30 November 2007 04:35 Go to next message
raghunalumachu
Messages: 12
Registered: November 2007
Location: MUMBAI
Junior Member
hi,
I am having two tables empmaster, emplmaster which are having two columns each as follows

empmaster-->empname
empcode

emplmaster-->ename
ecode

none of the ecode in both the tables will be same and they wont have matching data . i would like to have a query which on giving the employeecode either it may be ecode(emplmaster) or empcode(empmaster) should generate the employeename i.e. if its ecode it should give its ename
if its empcode it should give empname

Thanks in Advance
Raghu
Re: retrieval of employee name [message #284600 is a reply to message #284598] Fri, 30 November 2007 04:37 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
A UNION ALL query seems about right.

MHE
Re: retrieval of employee name [message #284603 is a reply to message #284598] Fri, 30 November 2007 04:47 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hmhm yes..


I prefer to create a view with UNION clause Smile
and retrieve from the view

Thumbs Up
Rajuvan
Re: retrieval of employee name [message #284605 is a reply to message #284603] Fri, 30 November 2007 04:51 Go to previous messageGo to next message
raghunalumachu
Messages: 12
Registered: November 2007
Location: MUMBAI
Junior Member
thank you guys but i am new to oracle and can any one of you give me the exact syntax plz
Re: retrieval of employee name [message #284607 is a reply to message #284605] Fri, 30 November 2007 04:54 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A correct SYNTAX? Have you ever heard of documentation? It is all in there; you only have to read it and apply what you have read.
Re: retrieval of employee name [message #284608 is a reply to message #284603] Fri, 30 November 2007 04:54 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
rajavu1 wrote on Fri, 30 November 2007 11:47

I prefer to create a view with UNION clause Smile
and retrieve from the view
You mean "UNION ALL", right?

MHE

[EDIT] before you ask where to find the documentation: it's in the forum guide Wink

[Updated on: Fri, 30 November 2007 04:55]

Report message to a moderator

Re: retrieval of employee name [message #284611 is a reply to message #284598] Fri, 30 November 2007 05:00 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

No even UNION will do the trick.

Quote:

none of the ecode in both the tables will be same and they wont have matching data .


ie, UNION and UNION ALL gives the same resultset Smile

Thumbs Up
Rajuvan.
Re: retrieval of employee name [message #284613 is a reply to message #284611] Fri, 30 November 2007 05:13 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
My script:
CREATE TABLE rajuvan_1 
AS
SELECT LEVEL col1
     , CAST(TO_CHAR(TO_DATE(LEVEL,'J'),'Jsp') AS VARCHAR2(50)) col2
FROM   dual
WHERE  MOD(LEVEL,2) = 0
CONNECT BY LEVEL <= 20000
/

CREATE TABLE rajuvan_2
AS
SELECT LEVEL col1
     , CAST(TO_CHAR(TO_DATE(LEVEL,'J'),'Jsp') AS VARCHAR2(50)) col2
FROM   dual
WHERE  MOD(LEVEL,2) = 1
CONNECT BY LEVEL <= 20000
/

CREATE VIEW rajuvan_vw
AS
SELECT col1
     , col2
FROM rajuvan_1
UNION
SELECT col1
     , col2
FROM rajuvan_2
/

SET AUTOT TRACE EXP
PROMPT Rajuvan's solution:
SELECT *
FROM rajuvan_vw
WHERE col1 = 798
/

PROMPT Maaher's solution:
SELECT col1
     , col2
FROM rajuvan_1
WHERE col1 = 798
UNION ALL
SELECT col1
     , col2
FROM rajuvan_2
WHERE col1 = 798
/

DROP VIEW rajuvan_vw
/

DROP TABLE rajuvan_1 PURGE
/
DROP TABLE rajuvan_2 PURGE
/

My test run:
SQL> @orafaq

Table created.


Table created.


View created.

Rajuvan's solution:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=42 Card=2 Bytes=80)
   1    0   VIEW OF 'RAJUVAN_VW' (VIEW) (Cost=42 Card=2 Bytes=80)
   2    1     SORT (UNIQUE) (Cost=42 Card=2 Bytes=80)
   3    2       UNION-ALL
   4    3         TABLE ACCESS (FULL) OF 'RAJUVAN_1' (TABLE) (Cost=20 Card=1 Bytes=40)
   5    3         TABLE ACCESS (FULL) OF 'RAJUVAN_2' (TABLE) (Cost=21 Card=1 Bytes=40)



Maaher's solution:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=40 Card=2 Bytes=80)
   1    0   UNION-ALL
   2    1     TABLE ACCESS (FULL) OF 'RAJUVAN_1' (TABLE) (Cost=20 Card=1 Bytes=40)
   3    1     TABLE ACCESS (FULL) OF 'RAJUVAN_2' (TABLE) (Cost=21 Card=1 Bytes=40)





View dropped.


Table dropped.


Table dropped.

SQL>
Notice any difference? I think it is good practice to use UNION ALL whenever you can, to avoid unnecessary sorts.

MHE

[Updated on: Fri, 30 November 2007 05:14]

Report message to a moderator

Re: retrieval of employee name [message #284617 is a reply to message #284598] Fri, 30 November 2007 05:22 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hats Off Neutral Once again

Thumbs Up
Rajuvan.
Previous Topic: format data with 4 decimal
Next Topic: Creating HTML page in Procedure using htp tags.
Goto Forum:
  


Current Time: Thu Dec 08 23:51:06 CST 2016

Total time taken to generate the page: 0.05595 seconds