Union All statement

[dsfamily]

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

[baabi_26]

Didn't understand your requirement. Can you explain a little bit. Do you have to execute this Insert..select stmt on your taget database so your menu_record table gets loaded. Is that it? To do that you can trigger the stage with a single row, single col source which will fire this SQL on the Target. Let us know if this is not what you want.

[chulett]

Run the UNION statement (without the INSERT ) in a single source stage using custom sql. Make sure your columns defined in the stage match the output of the union. From there, link that to another stage to do the actual insert into the target table.

[dsfamily]

I wanted to split it without union and then merge it.With union it takes it too much time and the purpose of datastage is defeated as it takes too much time

[ray.wurlod]

It doesn't really matter whether you have the database server perform the UNION or have DataStage perform it; the same amount of work has to be done.

In DataStage I'd suggest processing two separate streams. Even though you didn't specify UNION ALL in your SQL (you only had UNION), you need to handle the possibility on the second run that the key value is already in the table. But you'd have to do this anyway with your approach; UNION ALL can return duplicates.

What does 'Recorded Input' do? That there's a nested SELECT immediately following it suggest that it's a function, but I suspect that there's a missing "AND unique_id IN" somewhere around about there.

[dsfamily]

I tried feeding the same statement with the union in the datastage job.It takes more time than the stored procedure.I tried populating the keys using hash tables.I takes more time than the inner query.Any suggestion on this would be great.

[baabi_26]

As i said earlier..why not trigger the stmt in Database itself. Ofcourse, you cannot avoid Union with this approach. But what you can avoid is all the read and write operations that you are doing when you are fetching the data into the Datastage server.

Have a sequential file with a single col (say trig) and single row (value "1" would be good enough). Write your SQL (Insert..select) as User-Defined SQL in your target stage and run the job.

You know..now you can blame your DBA for not tuning the SQL