各位大神,我的问题终于解决了,我把三条命令合成一条命令返回,然后在报表中控制显示哪一部分,这样不会出现重复数据,也不用使用子报表了,谢谢各位的回帖。
源码如下:
SELECT 'services' AS TYPE,
F.FRM_NAME,
F.FRM_NUM,
F.FRM_NUM AS FORNUM,
NULL SITE_COD,
AVG(FLOOR(IR.RPT_RCV_DATETIME - R.RQST_DATETIME)) IRDAY,
C.SVC_NAME
FROM IVG_RQST R,
IVG_FRM F,
CIM_DATA.CLC_ENCNTR E,
CIM_DATA.COD_SVC C,
IVG_RPT IR
WHERE R.IVG_FRM_ID = F.IVG_FRM_ID
AND R.CLC_ENCNTR_ID = E.CLC_ENCNTR_ID
AND E.COD_SVC_ID = C.COD_SVC_ID
AND R.IVG_RQST_ID = IR.IVG_RQST_ID
AND ((SELECT CASE
WHEN '{?frenqucy}' = 'Yearly' THEN
(SELECT 1
FROM DUAL
WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'YYYY') = '{?pYear}')
WHEN '{?frenqucy}' = 'Monthly' THEN
(SELECT 1
FROM DUAL
WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'YYYY') = substr('{?pMonth}',1,4)
AND TO_CHAR(IR.RPT_RCV_DATETIME, 'MM') = substr('{?pMonth}',6,2)
)
WHEN '{?frenqucy}' = 'Daily' THEN
(SELECT 1
FROM DUAL
WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'yyyy-mm-dd') = TO_CHAR({?pDate}, 'yyyy-mm-dd'))
WHEN '{?frenqucy}' = 'User Specified' THEN
(SELECT 1
FROM DUAL
WHERE TRUNC(IR.RPT_RCV_DATETIME) BETWEEN
{?pStartDate} AND {?pEndDate})
END CASE
FROM DUAL) = 1)
GROUP BY F.FRM_NAME, F.FRM_NUM, C.SVC_NAME
UNION ALL
SELECT 'clinics' AS TYPE,
F.FRM_NAME,
F.FRM_NUM,
F.FRM_NUM AS FORNUM,
C.SITE_COD,
AVG(FLOOR(IR.RPT_RCV_DATETIME - R.RQST_DATETIME)) AS IRDAY,
NULL SVC_NAME
FROM IVG_RQST R,
IVG_FRM F,
CIM_DATA.CLC_ENCNTR E,
CIM_DATA.ORG_SITE C,
IVG_RPT IR
WHERE R.IVG_FRM_ID = F.IVG_FRM_ID
AND R.CLC_ENCNTR_ID = E.CLC_ENCNTR_ID
AND E.ORG_SITE_ID = C.ORG_SITE_ID
AND R.IVG_RQST_ID = IR.IVG_RQST_ID
AND ((SELECT CASE
WHEN '{?frenqucy}' = 'Yearly' THEN
(SELECT 1
FROM DUAL
WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'YYYY') = '{?pYear}')
WHEN '{?frenqucy}' = 'Monthly' THEN
(SELECT 1
FROM DUAL
WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'YYYY') = substr('{?pMonth}',1,4)
AND TO_CHAR(IR.RPT_RCV_DATETIME, 'MM') = substr('{?pMonth}',6,2))
WHEN '{?frenqucy}' = 'Daily' THEN
(SELECT 1
FROM DUAL
WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'yyyy-mm-dd') = TO_CHAR({?pDate}, 'yyyy-mm-dd'))
WHEN '{?frenqucy}' = 'User Specified' THEN
(SELECT 1
FROM DUAL
WHERE TRUNC(IR.RPT_RCV_DATETIME) BETWEEN
{?pStartDate} AND {?pEndDate})
END CASE
FROM DUAL) = 1)
GROUP BY F.FRM_NAME, F.FRM_NUM, C.SITE_COD
UNION ALL
SELECT 'forms' AS TYPE,
F.FRM_NAME,
F.FRM_NUM,
F.FRM_NUM as FORNUM,
NULL SITE_COD,
AVG(FLOOR(IR.RPT_RCV_DATETIME - R.RQST_DATETIME)) AS IRDAY,
NULL SVC_NAME
FROM IVG_RQST R, IVG_FRM F, IVG_RPT IR
WHERE R.IVG_FRM_ID = F.IVG_FRM_ID
AND R.IVG_RQST_ID = IR.IVG_RQST_ID
AND ((SELECT CASE
WHEN '{?frenqucy}' = 'Yearly' THEN
(SELECT 1
FROM DUAL
WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'YYYY') = '{?pYear}')
WHEN '{?frenqucy}' = 'Monthly' THEN
(SELECT 1
FROM DUAL
WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'YYYY') = substr('{?pMonth}',1,4)
AND TO_CHAR(IR.RPT_RCV_DATETIME, 'MM') = substr('{?pMonth}',6,2))
WHEN '{?frenqucy}' = 'Daily' THEN
(SELECT 1
FROM DUAL
WHERE TO_CHAR(IR.RPT_RCV_DATETIME, 'yyyy-mm-dd') = TO_CHAR({?pDate}, 'yyyy-mm-dd'))
WHEN '{?frenqucy}' = 'User Specified' THEN
(SELECT 1
FROM DUAL
WHERE TRUNC(IR.RPT_RCV_DATETIME) BETWEEN
{?pStartDate} AND {?pEndDate})
END CASE
FROM DUAL) = 1)
GROUP BY F.FRM_NAME, F.FRM_NUM