My statment takes too long to run.

Originally Posted by keithsavoy

-- Count of Exams initiated
SELECT DISTINCT
EXM.REGN,
EXM.EXM_COUNT_BY_REGN,
EXM3.DIV,
EXM3.EXM_COUNT_BY_DIV,
EXM2.TOTAL_EXM_COUNT,--,
--EXM4.EXM_TYPE,
--EXM4.EXM_COUNT_BY_EXM_TYPE

-- Count of Exams compleated
EXM5.REGN_COMPL,
EXM5.EXM_COUNT_BY_REGN_COMPL,
EXM7.DIV_COMPL,
EXM6.TOTAL_EXM_COUNT_COMPL ,
EXM7.EXM_COUNT_BY_DIV_COMPL, --,
--EXM8.EXM_TYPE,
--EXM8.EXM_COUNT_BY_EXM_TYPE_COMPL

--Count of Exams compleated with Action
EXM9.REGN_COMPL_W_ACT,
EXM9.EXM_COUNT_BY_REGN_COMPL_W_ACT,
EXM10.DIV_COMPL_W_ACT,
EXM10.EXM_COUNT_BY_DIV_COMPL_W_ACT,
EXM11.TOTAL_EXM_COUNT_COMPL_W_ACT

FROM
LECONS.CMPLN A,
(SELECT ENF_REGN.ENF_REGN_DESC AS REGN,
COUNT (CMPLN.ENF_REGN_ID) AS EXM_COUNT_BY_REGN
FROM
LECONS.CMPLN,
LECONS.ENF_REGN
WHERE CMPLN_NBR LIKE 'EX%'
AND CMPLN.ENF_REGN_ID = ENF_REGN.ENF_REGN_ID
GROUP BY (ENF_REGN.ENF_REGN_DESC))EXM,

(SELECT
COUNT (*) AS TOTAL_EXM_COUNT
FROM LECONS.CMPLN
WHERE CMPLN_NBR LIKE 'EX%'
)EXM2,

(SELECT
ENF_SECUR_CLS.ENF_SECUR_CLS_DESC AS DIV,
COUNT (CMPLN.ENF_REGN_ID) AS EXM_COUNT_BY_DIV
FROM LECONS.CMPLN,
LECONS.ENF_REGN,
LECONS.ENF_SECUR_CLS
WHERE CMPLN_NBR LIKE 'EX%'
AND CMPLN.ENF_REGN_ID = ENF_REGN.ENF_REGN_ID
AND CMPLN.ENF_SECUR_CLS_ID = ENF_SECUR_CLS.ENF_SECUR_CLS_ID
GROUP BY (ENF_SECUR_CLS.ENF_SECUR_CLS_DESC))EXM3,--,

--(SELECT CMPLN_ACTV_TYP.CMPLN_ACTV_TYP_DESC AS EXM_TYPE, -- ClientCode not configured yet for FK
--COUNT (CMPLN.ENF_REGN_ID) AS EXM_COUNT_BY_EXM_TYPE
--FROM LECONS.CMPLN, LECONS.ENF_REGN,LECONS.CMPLN_ACTV_TYP
--WHERE CMPLN_NBR LIKE 'EX%'
--AND CMPLN.ENF_REGN_ID = ENF_REGN.ENF_REGN_ID
--AND CMPLN.CLNT_CMPLN_APLD_TYP_ID = CMPLN_ACTV_TYP.CMPLN_ACTV_TYP_ID
--GROUP BY (CMPLN_ACTV_TYP.CMPLN_ACTV_TYP_DESC))EXM4

-- Count of Exams Compleated
(SELECT
ENF_REGN.ENF_REGN_DESC AS REGN_COMPL,
COUNT (CMPLN.ENF_REGN_ID) AS EXM_COUNT_BY_REGN_COMPL
FROM LECONS.CMPLN,
LECONS.ENF_REGN,
LECONS.CLNT_CMPLN_STA,
LECONS.CMPLN_STA
WHERE CMPLN_NBR LIKE 'EX%'
AND CMPLN.ENF_REGN_ID = ENF_REGN.ENF_REGN_ID
AND CMPLN.CLNT_CMPLN_STA_ID = CLNT_CMPLN_STA.CLNT_CMPLN_STA_ID
AND CLNT_CMPLN_STA.CMPLN_STA_ID = CMPLN_STA.CMPLN_STA_ID
GROUP BY (ENF_REGN.ENF_REGN_DESC))EXM5,

(SELECT COUNT (*) AS TOTAL_EXM_COUNT_COMPL
FROM LECONS.CMPLN,
LECONS.CLNT_CMPLN_STA,
LECONS.CMPLN_STA
WHERE CMPLN_NBR LIKE 'EX%'
AND CMPLN.CLNT_CMPLN_STA_ID = CLNT_CMPLN_STA.CLNT_CMPLN_STA_ID
AND CLNT_CMPLN_STA.CMPLN_STA_ID = CMPLN_STA.CMPLN_STA_ID
)EXM6,

(SELECT
ENF_SECUR_CLS.ENF_SECUR_CLS_DESC AS DIV_COMPL,
COUNT (CMPLN.ENF_REGN_ID) AS EXM_COUNT_BY_DIV_COMPL
FROM LECONS.CMPLN, LECONS.ENF_REGN,LECONS.ENF_SECUR_CLS,LECONS.CLNT_C MPLN_STA,LECONS.CMPLN_STA
WHERE CMPLN_NBR LIKE 'EX%'
AND CMPLN.ENF_REGN_ID = ENF_REGN.ENF_REGN_ID
AND CMPLN.ENF_SECUR_CLS_ID = ENF_SECUR_CLS.ENF_SECUR_CLS_ID
AND CMPLN.CLNT_CMPLN_STA_ID = CLNT_CMPLN_STA.CLNT_CMPLN_STA_ID
AND CLNT_CMPLN_STA.CMPLN_STA_ID = CMPLN_STA.CMPLN_STA_ID
GROUP BY (ENF_SECUR_CLS.ENF_SECUR_CLS_DESC))EXM7,--,

--(SELECT CMPLN_ACTV_TYP.CMPLN_ACTV_TYP_DESC AS EXM_TYPE_COMPL, -- ClientCode not configured yet for FK
--COUNT (CMPLN.ENF_REGN_ID) AS EXM_COUNT_BY_EXM_TYPE
--FROM LECONS.CMPLN, LECONS.ENF_REGN,LECONS.CMPLN_ACTV_TYP
--WHERE CMPLN_NBR LIKE 'EX%'
--AND CMPLN.ENF_REGN_ID = ENF_REGN.ENF_REGN_ID
--AND CMPLN.CLNT_CMPLN_APLD_TYP_ID = CMPLN_ACTV_TYP.CMPLN_ACTV_TYP_ID
--GROUP BY (CMPLN_ACTV_TYP.CMPLN_ACTV_TYP_DESC))EXM8

-- Count of Exams Compleated with Action taken
(SELECT
ENF_REGN.ENF_REGN_DESC AS REGN_COMPL_W_ACT,
COUNT (CMPLN.ENF_REGN_ID) AS EXM_COUNT_BY_REGN_COMPL_W_ACT
FROM LECONS.CMPLN,
LECONS.ENF_REGN,
LECONS.CLNT_CMPLN_STA,
LECONS.CMPLN_STA,
LECONS.CMPLN_ACTV
WHERE CMPLN_NBR LIKE 'EX%'
AND CMPLN.ENF_REGN_ID = ENF_REGN.ENF_REGN_ID
AND CMPLN.CLNT_CMPLN_STA_ID = CLNT_CMPLN_STA.CLNT_CMPLN_STA_ID
AND CLNT_CMPLN_STA.CMPLN_STA_ID = CMPLN_STA.CMPLN_STA_ID
AND CMPLN.CMPLN_ID = CMPLN_ACTV.CMPLN_ID
GROUP BY (ENF_REGN.ENF_REGN_DESC))EXM9,

(SELECT COUNT (*) AS TOTAL_EXM_COUNT_COMPL_W_ACT
FROM LECONS.CMPLN,
LECONS.CLNT_CMPLN_STA,
LECONS.CMPLN_STA,
LECONS.CMPLN_ACTV
WHERE CMPLN_NBR LIKE 'EX%'
AND CMPLN.CLNT_CMPLN_STA_ID = CLNT_CMPLN_STA.CLNT_CMPLN_STA_ID
AND CLNT_CMPLN_STA.CMPLN_STA_ID = CMPLN_STA.CMPLN_STA_ID
AND CMPLN.CMPLN_ID = CMPLN_ACTV.CMPLN_ID
)EXM11,

(SELECT
ENF_SECUR_CLS.ENF_SECUR_CLS_DESC AS DIV_COMPL_W_ACT,
COUNT (CMPLN.ENF_REGN_ID) AS EXM_COUNT_BY_DIV_COMPL_W_ACT
FROM LECONS.CMPLN,
LECONS.ENF_REGN,
LECONS.ENF_SECUR_CLS,
LECONS.CLNT_CMPLN_STA,
LECONS.CMPLN_STA,
LECONS.CMPLN_ACTV
WHERE CMPLN_NBR LIKE 'EX%'
AND CMPLN.ENF_REGN_ID = ENF_REGN.ENF_REGN_ID
AND CMPLN.ENF_SECUR_CLS_ID = ENF_SECUR_CLS.ENF_SECUR_CLS_ID
AND CMPLN.CLNT_CMPLN_STA_ID = CLNT_CMPLN_STA.CLNT_CMPLN_STA_ID
AND CLNT_CMPLN_STA.CMPLN_STA_ID = CMPLN_STA.CMPLN_STA_ID
AND CMPLN.CMPLN_ID = CMPLN_ACTV.CMPLN_ID
GROUP BY (ENF_SECUR_CLS.ENF_SECUR_CLS_DESC))EXM10--,

--(SELECT CMPLN_ACTV_TYP.CMPLN_ACTV_TYP_DESC AS EXM_TYPE_COMPL, -- ClientCode not configured yet for FK
--COUNT (CMPLN.ENF_REGN_ID) AS EXM_COUNT_BY_EXM_TYPE
--FROM LECONS.CMPLN, LECONS.ENF_REGN,LECONS.CMPLN_ACTV_TYP
--WHERE CMPLN_NBR LIKE 'EX%'
--AND CMPLN.ENF_REGN_ID = ENF_REGN.ENF_REGN_ID
--AND CMPLN.CLNT_CMPLN_APLD_TYP_ID = CMPLN_ACTV_TYP.CMPLN_ACTV_TYP_ID
--GROUP BY (CMPLN_ACTV_TYP.CMPLN_ACTV_TYP_DESC))EXM8
WHERE 1=1 ;