Oracle SQL Union/Merge With Duplicates Issue

Question

Hopefully someone can help me out.

So let's say I have temporary table named "TEMP_TABLE"

 EMP_ID     number
 EMP_FNAME  varchar2()
 EMP_LNAME  varchar2()

Records in "TEMP_TABLE"

1, Some, Guy
2, Some, Girl
3, Some, Animal

Through some SQL magic, I'm not going to delve into, those values are calculated and put into the TEMP_TABLE, the multiset of the a select * from TEMP_TABLE that is returned by a function

Let's say that function is FUNC_THAT_RETURNS_TABLE

Okay, so if I say Select * from table("FUNC_THAT_RETURNS_TABLE");

I am returned:

  1, Some, Guy
  2, Some, Girl
  3, Some, Animal

So far so good.

So now I create another table called "NEWLY_CREATED_TABLE"

By saying:

 Create Table "NEWLY_CREATED_TABLE" AS
 (Select * FROM table("FUNC_THAT_RETURNS_TABLE")); 

Note that the table is created from querying the function

So those three records should now be inside of NEWLY_CREATED_TABLE

The issue is that If I say:

Select * FROM NEWLY_CREATED_TABLE
Union
SELECT * FROM table("FUNC_THAT_RETURNS_TABLE");

The result set is:

 1, Some, Guy
 1, Some, Guy
 2, Some, Girl
 2, Some, Girl
 3, Some, Animal
 3, Some, Animal

Even though though the data is exactly the same, can somebody tell me what I'm missing?

I found the union issue when testing the possibility of using a merge. When I am trying to merge the data on the table, with the data from the function, all of the data in the table is being replaced. Which lead me to think that the code isn't identifying the unique records properly.

My merge code:

 Merge Into Newly_Created_Table a
 using
 (
   Select * from table(Func_That_Returns_TABLE)
 ) b
On (a.EMP_ID = b.EMP_ID)

When Matched....

When Not Matched....

** EDIT ** 9-7-2016 SHOUTOUT TO @Shannon Severance there was an empty space in one of the columns.

Are there any methods besides attempting to trim before an insert, truncate, or using a trigger before an insert to trim the :NEW value, to ensure that trailing white spaces won't be added for no apparent reason?

Answer 1

I got solution for you. I ran that and did not get any error.

CREATE TABLE temp_table
(
 EMP_ID     NUMBER,
 EMP_FNAME  varchar2(32),
 EMP_LNAME  varchar2(32)
);

INSERT INTO temp_table values (1, 'SOME','Guy');
INSERT INTO temp_table values (2, 'SOME','Girl');
INSERT INTO temp_table values (3, 'SOME','Animal');

CREATE OR REPLACE TYPE three_values_ot AS OBJECT
   (
 EMP_ID     NUMBER,
 EMP_FNAME  varchar2(32),
 EMP_LNAME  varchar2(32)
   );


CREATE OR REPLACE TYPE three_values_nt
   IS TABLE OF three_values_ot;

CREATE OR REPLACE FUNCTION FUNC_THAT_RETURNS_TABLE
   RETURN three_values_nt
IS
   l_return three_values_nt := 
      three_values_nt (three_values_ot (1, 'SOME','Guy'),
                       three_values_ot (2, 'SOME','Girl'),
                       three_values_ot (3, 'SOME','Animal'));
BEGIN
   RETURN l_return;
END;

SELECT * FROM temp_table
UNION
SELECT * FROM TABLE (FUNC_THAT_RETURNS_TABLE ());

Output of above union query is

1   SOME    Guy
2   SOME    Girl
3   SOME    Animal