SQL, How do I add a column in a table that contains two columns from another table (first name + last name)?

28Joplin
28Joplin used Ask the Experts™
on
I have created a table called EMPS that contains all the rows and columns currently in the employees table.  Now I need to add a column named Full_Name that contains the employee first name concatenated with the employee last name.  Any help to a newbie would be very helpful.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
(field1 + field2) as OneColumn
Top Expert 2011

Commented:
Oops sorry, I gave you sql server syntax.

Should be:

Column1 || Column2

Or you can add some space:

Column1 |' '| Column2
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Alter table NewTable add
Full_Name AS Coalesce(FirstName+' '+LastName, FirstName, LastName)

That puts a space between the two, change to comma or comma-space if you like.
The reason for coalesce is that if either one is null, it will still return the other part.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:

INSERT INTO emps
 (employees.first_name, employees.last_name) AS Full_Name
                                              *

ERROR at line 2:
ORA-00926: missing VALUES keyword ??

Author

Commented:

Alter table emps add
Full_Name AS Coalesce(FirstName+' '+LastName, FirstName, LastName)

Alter table emps add
*

ERROR at line 1:
ORA-00406: COMPATIBLE parameter needs to be 11.0.0.0.0 or greater
ORA-00722: Feature "Virtual columns"
??
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
So you want a new column, which will have first+last name.  When you add a new column, do you want it to automatically do the same for full name?  In that case, only Oracle 11 onwards supports virtual columns.  For 10 and prior, you need to use triggers.

For a once off population of full_name, use

(size accordingly, 1000 is very large)
alter table emps add full_name varchar2(1000)

update emps set full_name = case
when firstName is null then lastName
when lastName is null then firstName
else firstName || ' ' || lastName end

Author

Commented:
It's for Oracle 11.  Thanks much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial