Original expensive and slow query is this:
select 'INTEGRATED' as "STATUS", coalesce(sum(response_count),0) as "COUNT" from f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00','2015-08-05 01:00:00', null, 'LINK')
where wfc_result in ('TM_OK', 'TM_NO_CHANGE')
union
select 'WFC_FALLOUT' as "STATUS", coalesce(sum(response_count),0) as "COUNT" from f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00','2015-08-05 01:00:00', null, 'LINK')
where wfc_result LIKE 'WFC%'
union
select 'TM_FALLOUT' as "STATUS", coalesce(sum(response_count),0) as "COUNT" from f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00','2015-08-05 01:00:00', null, 'LINK')
where wfc_result = 'TM_FAIL'
union
select 'WFC_PENDING' as "STATUS", coalesce(sum(response_count),0) as "COUNT" from f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00','2015-08-05 01:00:00', null, 'LINK')
where wfc_result = 'PENDING';
Output:
Status Count
Integrated 40
TM_FALLOUT 50
WFC_PENDING 60
WFC_FALLOUT 70
The output format above is what I need. But this query takes a lot of time.
The query that I want to use is the following as it takes less time.
select
sum(CASE WHEN wfc_result IN ('TM_OK','TM_NO_CHANGE') THEN response_count ELSE 0 END) as "INTEGRATED",
sum(CASE WHEN wfc_result LIKE 'WFC%' THEN response_count ELSE 0 END) as "WFC_FALLOUT",
sum(CASE WHEN wfc_result = 'TM_FAIL' THEN response_count ELSE 0 END) as "TM_FALLOUT",
sum(CASE WHEN wfc_result = 'PENDING' THEN response_count ELSE 0 END) as "WFC_PENDING"
from f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00','2015-08-05 01:00:00',null, 'LINK')
However, the output of this query is as follows, I need the same output as I get from the first query.
Integrated WFC_FALLOUT TM_FALLOUT WFC_PENDING
40 50 60 70
I tried several ways but unable to find out how I can edit this?
Assuming that 'wfc_result' WFC% is always WFC_% yo can do the following:
SELECT status.lbl AS status, coalesce(sum(response_count), 0) AS count
FROM (VALUES
('TM_O', 'INTEGRATED'),
('TM_N', 'INTEGRATED'),
('WFC_', 'WFC_FALLOUT'),
('TM_F', 'TM_FALLOUT'),
('PEND', 'WFC_PENDING')) status(cls, lbl)
LEFT JOIN f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00', '2015-08-05 01:00:00', NULL, 'LINK') wfc
ON substr(wfc.wfc_result, 1, 4) = status.cls
GROUP BY 1;
If there are only a few 'wfc_result' strings LIKE 'WFC%' you could also spell them out (as well as the other classes) in the VALUES clause and then drop the substr() function in the JOIN clause.
To get the total output, the query is much simpler:
SELECT sum(response_count) AS total_count
FROM f_wfc_transaction_summary_isocc_ft_sum_no_tid('2015-07-22 00:00:00', '2015-08-05 01:00:00', NULL, 'LINK');
This does of course make another call to the expensive function, but there is no way to get around that on the database; in your client application you could simply sum up the "count" values returned from the previous query.