If the requirement to find the count from different tables avoid count and group by in inner query in which cost will be more we can avoid that by adding below changes
SELECT try.employee_id,
Officer_name,
(SELECT (FIRST_NAME_EN || MIDDLE_NAME_EN || LAST_NAME_EN)
FROM EMPLOYEE
WHERE employee_id = try.employee_id)
Name_en,
SUM (no_of_entry) no_of_entry,
SUM (no_of_exits) no_of_exits,
trg_date AS Transaction_date
FROM ( (SELECT au.employee_id,
AU.USER_GROUP_ID,
au.user_name Officer_name,
1 no_of_entry,
NULL no_of_exits,
NULL cs_application_count,
NULL no_entrt_permits,
NULL no_approved_ras,
NULL no_outpass_application,
NULL no_approved_nsd,
NULL no_approved_bl,
TRUNC (travel_date) trg_date
FROM travel_detail_log td, application_user au
WHERE td.created_by = AU.CREATED_BY
AND TRAVEL_TYPE_ID = 1
AND td.revtype <> 2
AND td.is_settled = 0)
UNION ALL
(SELECT au.employee_id,
AU.USER_GROUP_ID,
au.user_name Officer_name,
NULL no_of_entry,
1 no_of_exits,
NULL cs_application_count,
NULL no_entrt_permits,
NULL no_approved_ras,
NULL no_outpass_application,
NULL no_approved_nsd,
NULL no_approved_bl,
TRUNC (travel_date) trg_date
FROM travel_detail_log td, application_user au
WHERE td.created_by = AU.CREATED_BY
AND TRAVEL_TYPE_ID = 2
AND td.revtype <> 2
AND td.is_settled = 0)
) try
GROUP BY try.employee_id,
Officer_name,
trg_date,
user_group_id
Solution:
By adding group by in and sum outer query we can bring down the cost of the Query
SELECT try.employee_id,
Officer_name,
(SELECT (FIRST_NAME_EN || MIDDLE_NAME_EN || LAST_NAME_EN)
FROM EMPLOYEE
WHERE employee_id = try.employee_id)
Name_en,
SUM (no_of_entry) no_of_entry,
SUM (no_of_exits) no_of_exits,
trg_date AS Transaction_date
FROM ( (SELECT au.employee_id,
AU.USER_GROUP_ID,
au.user_name Officer_name,
1 no_of_entry,
NULL no_of_exits,
NULL cs_application_count,
NULL no_entrt_permits,
NULL no_approved_ras,
NULL no_outpass_application,
NULL no_approved_nsd,
NULL no_approved_bl,
TRUNC (travel_date) trg_date
FROM travel_detail_log td, application_user au
WHERE td.created_by = AU.CREATED_BY
AND TRAVEL_TYPE_ID = 1
AND td.revtype <> 2
AND td.is_settled = 0)
UNION ALL
(SELECT au.employee_id,
AU.USER_GROUP_ID,
au.user_name Officer_name,
NULL no_of_entry,
1 no_of_exits,
NULL cs_application_count,
NULL no_entrt_permits,
NULL no_approved_ras,
NULL no_outpass_application,
NULL no_approved_nsd,
NULL no_approved_bl,
TRUNC (travel_date) trg_date
FROM travel_detail_log td, application_user au
WHERE td.created_by = AU.CREATED_BY
AND TRAVEL_TYPE_ID = 2
AND td.revtype <> 2
AND td.is_settled = 0)
) try
GROUP BY try.employee_id,
Officer_name,
trg_date,
user_group_id
Solution:
By adding group by in and sum outer query we can bring down the cost of the Query
No comments:
Post a Comment