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  |
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 #306064 is a reply to message #306062] |
Wed, 12 March 2008 13:07  |
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
Regards
Raj
|
|
|
Goto Forum:
Current Time: Thu Feb 06 13:06:55 CST 2025
|