Original sql:
select *
from ((select count(c.customer_id) day30
from customer c
where c.is_active = '0'
and c.birth IN (SELECT to_char(SYSDATE - 1 + rownum, 'mm-dd')
FROM dual
CONNECT BY rownum <= 30)
AND exists (SELECT 1
FROM sid_view v
WHERE v.object_id_identity = c.customer_id
AND v.class =
'##########################' /* sensitivity information */
and v.ACE_SID in (:1, :2))) a left join
(select count(c.customer_id) tomorrow
from customer c
where c.is_active = '0'
and c.birth = to_char(sysdate + 1, 'mm-dd')
AND exists (SELECT 1
FROM sid_view v
WHERE v.object_id_identity = c.customer_id
AND v.class =
'##########################'
and v.ACE_SID in (:3, :4))) b on 1 = 1 left join
(select count(c.customer_id) today
from customer c
where c.is_active = '0'
and c.birth = to_char(sysdate, 'mm-dd')
AND exists (SELECT 1
FROM sid_view v
WHERE v.object_id_identity = c.customer_id
AND v.class =
'##########################'
and v.ACE_SID in (:5, :6))) on 1 = 1)
I known that v.ace_sid in ( value, value) being same by communicating with programer, therefore i extracted the global temp table as the sql_tmp
eg:
WITH sql_tmp AS
(SELECT c.customer_id, C.BIRTH
FROM customer c
WHERE c.is_active = '0'
AND c.birth IN (SELECT TO_CHAR(sysdate - 1 + rownum, 'mm-dd')
FROM dual
CONNECT BY rownum <= 30)
AND EXISTS
(SELECT 1
FROM sid_view v
WHERE v.object_id_identity = c.customer_id
AND v.class = '#######################'
AND V.ACE_SID IN (:1, :2)))
SELECT *
FROM (SELECT COUNT(T.CUSTOMER_ID) DAY30 FROM SQL_TMP t)
LEFT JOIN (SELECT COUNT(T.CUSTOMER_ID) TOMORROW
FROM SQL_TMP T
WHERE t.BIRTH = TO_CHAR(SYSDATE + 1, 'mm-dd' ) )
ON 1 = 1
LEFT JOIN (SELECT COUNT(t.customer_id) today FROM sql_tmp t
WHERE t.birth = to_char(sysdate, 'mm-dd'))
ON 1 = 1;