I'm working on Open Text Content Server Tool that uses PL/SQL Database. What I am trying to do is to fetch count data through 6 different queries all having different conditions and different tables too. I was trying to combine all these 6 count queries but no luck. Below are those 6 queries listed :
Documents Created in A Month:
select count (dataid) from Dtree where
Createdate >= %1 and createdate <= %2 and subtype = 144
Total No of Users:
select count(a.id) from Kuaf a, kuaf b where
a.deleted =0 and a.type =0 and b.id = a.groupid
Unique Users Logged in a Month(Count):
Select count (distinct (performerID))
from dauditnew where auditid=23 and auditdate >= %1 and auditdate <= %2
Users Created in a Month(Count):
Select Count(dataid) FROM DAUDITNEW where AUDITID = 1
AND AUDITSTR LIKE 'Create' and subtype=142 AND
auditdate >= %1 and auditdate <= %2
Users Deleted(Count):
SELECT count(a.userid) from dauditnew a WHERE
a.auditstr = 'Delete' AND
a.AuditDate >= %1 AND
a.AuditDate <= %2 AND
a.UserID in (Select ID from KUAF where Deleted = 1 and Type=0)
Workflows Initiated:
Select count(*) from Wworkaudit WWA where WWA.workaudit_status=1 AND
WWA.workaudit_date >= %1 and WWA.workaudit_date <= %2
Here %1,%2 denote user inputs. Since these 6 queries all have very different conditions, it seems a daunting task for me to combine them. Please help me out.
Thank You.
SELECT (
select count (dataid)
from Dtree
where Createdate BETWEEN :start_date and :end_date
and subtype = 144
) AS Docs_Per_Month,
(
select count(a.id)
from Kuaf a INNER JOIN kuaf b ON (b.id = a.groupid)
where a.deleted = 0
and a.type = 0
) AS Total_No_of_Users,
(
Select count( distinct performerID )
from dauditnew
where auditid = 23
and auditdate BETWEEN :start_date and :end_date
) AS Unique_Users_in_Month,
(
Select Count(dataid)
FROM DAUDITNEW
where AUDITID = 1
AND AUDITSTR = 'Create'
and subtype = 142
AND auditdate BETWEEN :start_date and :end_date
) AS Users_Created_in_Month,
(
SELECT count(a.userid)
from dauditnew a
WHERE a.auditstr = 'Delete'
AND a.auditdate BETWEEN :start_date and :end_date
AND a.UserID in (Select ID from KUAF where Deleted = 1 and Type=0)
) AS Users_Deleted,
(
Select count(*)
from Wworkaudit
where workaudit_status = 1
AND workaudit_date BETWEEN :start_date and :end_date
) AS Workflows_Initiated
FROM DUAL;