Union All statement
Posted: Fri Jun 03, 2005 11:13 am
I want to run this union all statement
INSERT INTO menu_record
(CALL_ID, APPLICATION_NAME, MENU_TIME, TRANS_ID, MENU_ID, MENU_NAME, MENU_STEP,
MENU_COMPONENT, MENU_RESULT, MENU_USER_INPUT, MENU_CONFIDENCE, MENU_INPUT_MODE)
SELECT unique_id as call_id, transaction_name as application_name, call_time as menu_time, null as menu_input_mode FROM reporting_temp (nolock)
WHERE event = 'DM Start' and unique_id in (SELECT DISTINCT unique_id FROM daily_uid_list)
UNION
SELECT unique_id as call_id, transaction_name as application_name,call_time as menu_time, substring(module_bit, 3, 10) as menu_id, substring(module_name, 9, charindex('.', module_name) - 9) as menu_name, 'INPUT' as menu_step, null as menu_component, null as menu_result, substring(outcome_data2, 11, 100) as user_input,
null as menu_confidence, substring(outcome_data4, 11, 5) as input_mode
FROM reporting_temp WHERE event = 'Recorded Input' (SELECT DISTINCT unique_id FROM daily_uid_list WHERE call_time >= cONVERT(VARCHAR(10),getdate() - @num_days, 102) AND call_time < CONVERT(VARCHAR(10),getdate() - @next_day, 102))
I have put into 2 oledb stages and used link collector.I have used sort merge keys.I am not getting the desired result.Do you have any suggestions to do this
INSERT INTO menu_record
(CALL_ID, APPLICATION_NAME, MENU_TIME, TRANS_ID, MENU_ID, MENU_NAME, MENU_STEP,
MENU_COMPONENT, MENU_RESULT, MENU_USER_INPUT, MENU_CONFIDENCE, MENU_INPUT_MODE)
SELECT unique_id as call_id, transaction_name as application_name, call_time as menu_time, null as menu_input_mode FROM reporting_temp (nolock)
WHERE event = 'DM Start' and unique_id in (SELECT DISTINCT unique_id FROM daily_uid_list)
UNION
SELECT unique_id as call_id, transaction_name as application_name,call_time as menu_time, substring(module_bit, 3, 10) as menu_id, substring(module_name, 9, charindex('.', module_name) - 9) as menu_name, 'INPUT' as menu_step, null as menu_component, null as menu_result, substring(outcome_data2, 11, 100) as user_input,
null as menu_confidence, substring(outcome_data4, 11, 5) as input_mode
FROM reporting_temp WHERE event = 'Recorded Input' (SELECT DISTINCT unique_id FROM daily_uid_list WHERE call_time >= cONVERT(VARCHAR(10),getdate() - @num_days, 102) AND call_time < CONVERT(VARCHAR(10),getdate() - @next_day, 102))
I have put into 2 oledb stages and used link collector.I have used sort merge keys.I am not getting the desired result.Do you have any suggestions to do this