Home » SQL & PL/SQL » SQL & PL/SQL » Insert into when there is a Union in the stmt
Insert into when there is a Union in the stmt [message #306060] Wed, 12 March 2008 13:00 Go to next message
bogey
Messages: 53
Registered: February 2006
Member
How can I take the results of my query an insert them into a new table:

select
profile_id, name_title, name_first, name_middle, name_last,
name_suffix, address_line_1, address_line_2, address_city,
address_state, address_zip_code, home_telephone, birth_date, gender,
NULL AS person_id, SUBSTR (ssn, LENGTH (ssn) - 4) AS ssn_last5,
edu.description AS education_level, update_date, update_source,
1 AS authoritativeness
FROM id_profile_users p, id_profile_xref_edu_levels edu
WHERE p.education_level = edu.edu_code
----------
UNION ALL
----------
SELECT 'FIS-' || person_id AS person_id, NULL AS name_title,
CASE
WHEN INSTR (first_name, ',') > 0
THEN TRIM (SUBSTR (first_name,
1,
(INSTR (first_name, ',') - 1)
)
)
ELSE first_name
END AS name_first,
CASE
WHEN INSTR (first_name, ',') > 0
THEN TRIM (SUBSTR (first_name, (INSTR (first_name, ',') + 1))
)
ELSE ''
END AS name_middle,
last_name AS name_last, NULL AS name_suffix,
address_line_1 AS address_line_1, NULL AS address_line_2,
address_city AS address_city, address_state AS address_state,
address_zip_code AS address_zip, home_telephone AS phone_number,
TO_DATE (birth_date, 'mmddyyyy') AS birth_date, gender, person_id,
SUBSTR ((ssn),
LENGTH ((ssn)) - 4
),
education_level, time_added_fis AS date_updated,
'Sam' AS updated_by,
CASE SUBSTR (assignment_status, 1, 10)
WHEN 'Terminated'
THEN 1
ELSE 10
END AS authoritativeness
FROM dev.v_base_employee
WHERE current_record = 'Y'
Re: Insert into when there is a Union in the stmt [message #306062 is a reply to message #306060] Wed, 12 March 2008 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm really really sad and tired tonight.

Do you think it is an expert question?
It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them.

Regards
Michel
Re: Insert into when there is a Union in the stmt [message #306063 is a reply to message #306060] Wed, 12 March 2008 13:05 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
insert /*+ append */ into <table_name>
(col1, col2, col3, ...)
select col1, col2, col3 ... from <table_a>
union all
select col1, col2, col3 ... from <table_b>

Read the forums guide before you post.

Regards

Raj
Re: Insert into when there is a Union in the stmt [message #306064 is a reply to message #306062] Wed, 12 March 2008 13:07 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

I'm really really sad and tired tonight.

Do you think it is an expert question?
It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them.


I admire your patience Cool

Regards

Raj
Previous Topic: SQL - Need Help Urgently
Next Topic: varchar2(1), char(1)
Goto Forum:
  


Current Time: Tue Dec 06 12:39:01 CST 2016

Total time taken to generate the page: 0.10870 seconds