| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889 |
- [18:50:26] DB Error Occured. FILE: VDSS020MF.cpp LINE: 990 FUNC: TVDSS020M::DispYearStatistics
- Title: VDS-검지기 교통량 통계 [VDSS020M]
- Class: EDatabaseError
- Error: ADOM: Cannot perform this operation on an open dataset
- SQL :
- SELECT LY.VDS_CTLR_NMBR, LY.VDS_CTLR_ID, LY.LCTN, LY.DTCT_NMBR, LY.ISTL_LANE, LY.VDS_DTCT_NM,
- LY.DTCT_TYPE, LY.DTCT_TYPE_NM, LX.TRFTYPE, LY.VDS_CTLR_NMBR || '.' || LY.LCTN AS NM,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(TOTTFVL, 0, 0, TOTTFVL)) AS TOTTFVL,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(AVGTFVL, 0, 0, AVGTFVL)) AS AVGTFVLX,
- DECODE(NVL(L01,0)+NVL(L02,0)+NVL(L03,0)+NVL(L04,0)+NVL(L05,0)+NVL(L06,0)+NVL(L07,0)+
- NVL(L08,0)+NVL(L09,0)+NVL(L10,0)+NVL(L11,0)+NVL(L12,0), 0, 0,
- ROUND(
- (NVL(L01,0)+NVL(L02,0)+NVL(L03,0)+NVL(L04,0)+NVL(L05,0)+NVL(L06,0)+NVL(L07,0)+
- NVL(L08,0)+NVL(L09,0)+NVL(L10,0)+NVL(L11,0)+NVL(L12,0)) /
- (DECODE(L01,NULL,0,1)+DECODE(L02,NULL,0,1)+DECODE(L03,NULL,0,1)+
- DECODE(L04,NULL,0,1)+DECODE(L05,NULL,0,1)+DECODE(L06,NULL,0,1)+
- DECODE(L07,NULL,0,1)+DECODE(L08,NULL,0,1)+DECODE(L09,NULL,0,1)+
- DECODE(L10,NULL,0,1)+DECODE(L11,NULL,0,1)+DECODE(L12,NULL,0,1)), 0)) AS AVGTFVL,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L01, NULL, 0, L01)) AS C01,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L02, NULL, 0, L02)) AS C02,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L03, NULL, 0, L03)) AS C03,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L04, NULL, 0, L04)) AS C04,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L05, NULL, 0, L05)) AS C05,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L06, NULL, 0, L06)) AS C06,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L07, NULL, 0, L07)) AS C07,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L08, NULL, 0, L08)) AS C08,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L09, NULL, 0, L09)) AS C09,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L10, NULL, 0, L10)) AS C10,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L11, NULL, 0, L11)) AS C11,
- DECODE(LX.DTCT_NMBR, NULL, 0, DECODE(L12, NULL, 0, L12)) AS C12
- FROM (
- SELECT GROUPING(DTCT_NMBR) AS GROUPING_DTCT_NMBR,
- GROUPING(ISTL_LANE) AS GROUPING_ISTL_LANE,
- GROUPING(TRFTYPE) AS GROUPING_TRFTYPE,
- CASE WHEN GROUPING(DTCT_NMBR ) = 1 THEN '검지기' ELSE DTCT_NMBR END AS GROUP_DTCT_NMBR,
- CASE WHEN GROUPING(ISTL_LANE) = 1 THEN '차로' ELSE ISTL_LANE END AS GROUP_ISTL_LANE,
- CASE WHEN GROUPING(TRFTYPE) = 1 THEN '소계' ELSE TRFTYPE END AS GROUP_TRFTYPE,
- DTCT_NMBR, ISTL_LANE, TRFTYPE,
- SUM(TFVL) TOTTFVL,
- ROUND(AVG(TFVL), 0) AVGTFVL,
- SUM(DECODE(STATDAY, '01', TFVL)) AS L01,
- SUM(DECODE(STATDAY, '02', TFVL)) AS L02,
- SUM(DECODE(STATDAY, '03', TFVL)) AS L03,
- SUM(DECODE(STATDAY, '04', TFVL)) AS L04,
- SUM(DECODE(STATDAY, '05', TFVL)) AS L05,
- SUM(DECODE(STATDAY, '06', TFVL)) AS L06,
- SUM(DECODE(STATDAY, '07', TFVL)) AS L07,
- SUM(DECODE(STATDAY, '08', TFVL)) AS L08,
- SUM(DECODE(STATDAY, '09', TFVL)) AS L09,
- SUM(DECODE(STATDAY, '10', TFVL)) AS L10,
- SUM(DECODE(STATDAY, '11', TFVL)) AS L11,
- SUM(DECODE(STATDAY, '12', TFVL)) AS L12
- FROM (
- SELECT TO_CHAR(Y.VDS_DTCT_NMBR) AS DTCT_NMBR, Y.VDS_DTCT_NM,
- TO_CHAR(Y.ISTL_LANE) AS ISTL_LANE, X.TRFTYPE, X.STATDAY, X.TFVL
- FROM (SELECT /*+ INDEX(A PK_VDS_DTCT_MN_STAT) */
- 'DTCT' AS TRFTYPE,
- A.VDS_DTCT_NMBR AS VDS_DTCT_NMBR, SUBSTR(A.STAT_DT, 5, 2) AS STATDAY,
- A.TFVL AS TFVL
- FROM TB_VDS_DTCT_MN_STAT A
- WHERE 1=1
- AND A.STAT_DT BETWEEN :p01 AND :p02
- ) X,
- TB_VDS_DTCT Y
- WHERE 1=1
- AND Y.DEL_YN = 'N'
- AND Y.VDS_DTCT_NMBR = X.VDS_DTCT_NMBR
- )
- GROUP BY ROLLUP (DTCT_NMBR, ISTL_LANE, TRFTYPE)
- HAVING GROUPING(TRFTYPE) = 0
- ) LX,
- (
- SELECT A.VDS_CTLR_NMBR, A.VDS_CTLR_ID, A.VDS_NM AS LCTN, B.VDS_DTCT_NMBR AS DTCT_NMBR,
- B.ISTL_LANE, B.VDS_DTCT_NM, B.DTCT_TYPE,
- (SELECT CMMN_CD_KOR_NM
- FROM TB_CMMN_CD
- WHERE CMMN_CLSF_CD = 'ACD'
- AND CMMN_CD = B.DTCT_TYPE) AS DTCT_TYPE_NM
- FROM TB_VDS_CTLR A,
- TB_VDS_DTCT B
- WHERE 1=1
- AND A.DEL_YN = 'N'
- AND B.DEL_YN = 'N'
- AND A.VDS_CTLR_NMBR = B.VDS_CTLR_NMBR
- ) LY
- WHERE LY.DTCT_NMBR = LX.DTCT_NMBR
- ORDER BY LY.VDS_CTLR_NMBR, LY.DTCT_NMBR, LY.ISTL_LANE
|
