weixin_43372376
Louis_Mo
采纳率0%
2019-05-06 12:21

SQL查询出错:java.lang.Exception: ERROR:ORA-03001: 未实施的功能

以下是报错信息:
图片说明

以下是SQL代码:

WITH TMP_TABLE_INFO AS
 (SELECT DISTINCT T.CITY_NAME,
                  T1.PRICE_DATE,
                  T1.YM_ID,
                  T2.VERSION_CODE,
                  T2.VERSION_ID,
                  PKG_COMMON_ROUTINE.FUNC_NEW_MSRP(T1.PRICE_DATE,
                                                   T2.VERSION_ID) AS MSRP
    FROM SJZX.V_REGION T
    LEFT JOIN SJZX.T_NEW_ENERGY_REPORT_VERSION T1
      ON 1 = 1
    LEFT JOIN SJZX.V_VERSION T2
      ON T2.VERSION_CODE = T1.VERSION_CODE
   WHERE T.CITY_NAME IN ('北京市',
                         '上海市',
                         '广州市',
                         '深圳市',
                         '天津市',
                         '成都市',
                         '西安市',
                         '杭州市',
                         '武汉市',
                         '济南市',
                         '昆明市',
                         '青岛市',
                         '合肥市',
                         '重庆市',
                         '长沙市',
                         '郑州市',
                         '南京市',
                         '长春市',
                         '苏州市',
                         '南昌市',
                         '宁波市',
                         '福州市')
     AND T1.YM_ID IN (201812, 201901, 201902)),
TMP_MSRP AS
 (SELECT DISTINCT YM_ID, SUB_MODEL_ID, 最低MSRP, 最高MSRP
    FROM (SELECT YM_ID,
                 VERSION_CODE,
                 VERSION_ID,
                 SUB_MODEL_ID,
                 MAX(VERSION_ID) KEEP(DENSE_RANK FIRST ORDER BY MSRP) OVER(PARTITION BY YM_ID, SUB_MODEL_ID) AS 最低MSRP,
                 MAX(VERSION_ID) KEEP(DENSE_RANK LAST ORDER BY MSRP) OVER(PARTITION BY YM_ID, SUB_MODEL_ID) AS 最高MSRP
            FROM (SELECT DISTINCT T.VERSION_CODE,
                                  T1.VERSION_ID,
                                  T1.SUB_MODEL_ID,
                                  T.YM_ID,
                                  PKG_COMMON_ROUTINE.FUNC_NEW_MSRP(T.PRICE_DATE,
                                                                   T1.VERSION_ID) AS MSRP
                    FROM SJZX.T_NEW_ENERGY_REPORT_VERSION T
                    LEFT JOIN SJZX.V_VERSION T1
                      ON T.VERSION_CODE = T1.VERSION_CODE
                   WHERE T.YM_ID IN (201812, 201901, 201902)))),
TMP_CITY_PRICE AS
 (SELECT A.PRICE_DATE,
         B.VERSION_CODE,
         B.VERSION_ID,
         B.SUB_MODEL_ID,
         C.CITY_NAME,
         C.CITY_ENAME,
         A.MARKET_PRICE * 10000 TP
    FROM SJZX.V_TP_CITY A, SJZX.V_VERSION B, SJZX.V_REGION C
   WHERE A.VERSION_ID = B.VERSION_ID
     AND A.CITY_ID = C.CITY_ID
     AND A.PRICE_INPUT_DATE_ID IN (2)
     AND A.PRICE_DATE BETWEEN 20181230 AND 20190228
     AND B.VERSION_CODE IN
         (SELECT DISTINCT VERSION_CODE FROM SJZX.T_NEW_ENERGY_REPORT_VERSION)
     AND C.CITY_NAME IN ('北京市',
                         '上海市',
                         '广州市',
                         '深圳市',
                         '天津市',
                         '成都市',
                         '西安市',
                         '杭州市',
                         '武汉市',
                         '济南市',
                         '昆明市',
                         '青岛市',
                         '合肥市',
                         '重庆市',
                         '长沙市',
                         '郑州市',
                         '南京市',
                         '长春市',
                         '苏州市',
                         '南昌市',
                         '宁波市',
                         '福州市')),
TMP_MIX AS
 (SELECT T.YM_ID,
         T1.SUB_MODEL_ID,
         T.VERSION_CODE,
         T2.SALES_QTY / NULLIF(SUM(T2.SALES_QTY)
                               OVER(PARTITION BY T1.SUB_MODEL_ID, T.YM_ID),
                               0) AS MIX
    FROM SJZX.T_NEW_ENERGY_REPORT_VERSION T
    LEFT JOIN SJZX.V_VERSION T1
      ON T1.VERSION_CODE = T.VERSION_CODE
    LEFT JOIN SJZX.V_ISABS_VERSION_SALES T2
      ON T2.VERSION_ID = T1.VERSION_ID
     AND T2.YM_ID = T.YM_ID
   WHERE T.YM_ID BETWEEN 201812 AND 201902),
TMP_SUBSIDIES AS
 (SELECT TO_CHAR(T1.PRICE_DATE, 'YYYYMMDD') AS PRICE_DATE,
         T3.CITY_NAME,
         T1.VERSION_ID,
         T1.LOCAL_SUBSIDIES,
         T1.NATION_SUBSIDIES,
         T2.OEM_SUBSIDY,
         T2.FAVOURABLE_CBT,
         AVG(T2.GUIDING_PRICE) MSRP,
         AVG(T1.TP * 10000) CP
    FROM SJZX.FDW_NEW_ENERGY_PRICE_MANAGE T1
    LEFT JOIN SJZX.FDW_NEW_ENERGY_CAR_TYPE T2
      ON T1.VERSION_ID = T2.VERSION_ID
     AND T1.CITY_ID = T2.CITY_ID
     AND T1.PRICE_DATE BETWEEN T2.START_EXECUTE_DATE AND T2.END_EXECUTE_DATE
    LEFT JOIN SJZX.V_REGION T3
      ON T3.CITY_ID = T1.CITY_ID
   GROUP BY T1.VERSION_ID,
            T3.CITY_NAME,
            T1.PRICE_DATE,
            T1.LOCAL_SUBSIDIES,
            T1.NATION_SUBSIDIES,
            T2.OEM_SUBSIDY,
            T2.FAVOURABLE_CBT)

SELECT T0.VERSION_CODE,
       CASE
         WHEN T.PRODUCTION_AND_MARKETING LIKE '%在销%' THEN
          'On sale'
         ELSE
          'Stopped Sale'
       END SALES_STATUS,
       T0.CITY_NAME,
       SUBSTR(T0.YM_ID, 1, 4) AS S_YEAR,
       TO_NUMBER(SUBSTR(T0.YM_ID, 5, 2)) AS S_MONTH,
       T0.YM_ID,
       CASE
         WHEN T.MANF_BRAND_PROP_NAME LIKE '%进口%' THEN
          'IMP'
         ELSE
          'CKD'
       END PRODUCT_TYPE,
       '' AS DEALER,
       T.MANF_GROUP_ENAME,
       T.BRAND_ORIG_ENAME,
       T.MANF_ENAME,
       T.BRAND_ENAME,
       T.MODEL_ENAME,
       T.SUB_MODEL_ENAME,
       T.VERSION_FULL_ENAME,
       CASE
         WHEN T0.VERSION_ID = T2.最低MSRP THEN
          'Entry'
         WHEN T0.VERSION_ID = T2.最高MSRP THEN
          'Top'
         ELSE
          '-'
       END ENTRY_LINE,
       T.SEGMENT_NAME,
       T.BODY_TYPE_ENAME,
       TO_DATE(T.LAUNCH_DATE, 'YYYY-MM-DD') AS LAUNCH_DATE,
       T.FUEL_TYPE_ENAME,
       T1.电机,
       T1.排量,
       T1.最大功率,
       T1.马力,
       T1.电机马力,
       T1.电机的最大功率,
       T1."纯电续航里程(km)",
       T1."电池电量(电能)(kwh)",
       T1.驱动形式,
       T1.车长,
       T1.车宽,
       T1.车高,
       T1.轴距,
       T1.车辆总质量,
       T1.整备质量,
       T1."最高车速(km/h)",
       T1."纯电行驶最高速度(km/h)",
       T1.电池包能量密度,
       T5.CP,
       T0.MSRP,
       T3.MIX,
       T4.TP,
       T5.NATION_SUBSIDIES,
       T5.LOCAL_SUBSIDIES,
       T5.FAVOURABLE_CBT,
       T5.OEM_SUBSIDY
  FROM TMP_TABLE_INFO T0
  LEFT JOIN SJZX.V_VERSION T
    ON T.VERSION_CODE = T0.VERSION_CODE
  LEFT JOIN SJZX.V_NEW_ENERGY_REPORT_CONFIG T1
    ON T1.VERSION_ID = T.VERSION_ID
  LEFT JOIN TMP_MSRP T2
    ON T2.SUB_MODEL_ID = T.SUB_MODEL_ID
   AND T2.YM_ID = T0.YM_ID
  LEFT JOIN TMP_MIX T3
    ON T3.YM_ID = T0.YM_ID
   AND T3.VERSION_CODE = T0.VERSION_CODE
  LEFT JOIN TMP_CITY_PRICE T4
    ON T4.PRICE_DATE = T0.PRICE_DATE
   AND T4.VERSION_CODE = T0.VERSION_CODE
   AND T4.CITY_NAME = T0.CITY_NAME
  LEFT JOIN TMP_SUBSIDIES T5
    ON T5.VERSION_ID = T.VERSION_ID
   AND T5.PRICE_DATE = T0.PRICE_DATE
   AND T5.CITY_NAME = T0.CITY_NAME
 ORDER BY T0.YM_ID, T.MANF_ENAME, T.BRAND_ENAME, T0.CITY_NAME

求大神解决~~~~~~感激不尽!!!!!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • null111666 null111666 2年前

    sql 太长,很有可能某个地方写错了,导致无法运行

    点赞 1 评论 复制链接分享

相关推荐