论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2010-06-28 20:36 |只看该作者 |倒序浏览

下面一个查询会报错ORA-00979: not a GROUP BY expression

SELECT T.TIME,
   T.CARRIER,
   T.CITYNAME,
   T.SCC_TERMINAL,
   NVL((SELECT NXSC.PRICE
         FROM XSCQUOTATION NXSC
         WHERE NXSC.CODE = T.CODE
            AND NXSC.FIELD1 = T.CITYNAME
            AND NXSC.CATENTRY_TYPE = 'terminal'),
         (SELECT NXSC.PRICE
            FROM XSCQUOTATION NXSC
         WHERE NXSC.CODE = T.CODE
            AND NXSC.FIELD1 = '省外'
            AND NXSC.CATENTRY_TYPE = 'terminal')) * T.SCC_TERMINAL SCC_TERMINAL_PRIICE
  FROM (SELECT TO_CHAR(ITEM.TIMESHIPPED, 'yyyy-mm') TIME,
            SHIPMODE.CODE,
            SHIPMODE.CARRIER,
            SUBSTR(XADDRESS.CITY, 1, 2) CITYNAME,
            SUM(CASE
                  WHEN RELEASE.STATUS = 'SSUC' AND
                        (ITEM.DESCRIPTION IN ('1', '3') OR
                        (ITEM.DESCRIPTION IN ('4') AND
                        S.FIELD1 NOT IN ('200', '755', '750', '754', '752'))) THEN
                  1
                  ELSE
                  0
               END) SCC_TERMINAL,
            SUM(CASE
                  WHEN RELEASE.STATUS = 'SFAL' AND RELEASE.FIELD2 = '14' AND
                        (ITEM.DESCRIPTION IN ('1', '3') OR
                        (ITEM.DESCRIPTION IN ('4') AND
                        S.FIELD1 NOT IN ('200', '755', '750', '754', '752'))) THEN
                  1
                  ELSE
                  0
               END) FAIL_TERMINAL,
            SUM(CASE
                  WHEN RELEASE.STATUS = 'SSUC' AND
                        (ITEM.DESCRIPTION IN ('21', '22') OR
                        (ITEM.DESCRIPTION IN ('12') AND
                        S.FIELD1 NOT IN ('755', '750', '754', '752'))) THEN
                  1
                  ELSE
                  0
               END) SCC_CARD,
            SUM(CASE
                  WHEN RELEASE.STATUS = 'SFAL' AND RELEASE.FIELD2 = '14' AND
                        (ITEM.DESCRIPTION IN ('21', '22') OR
                        (ITEM.DESCRIPTION IN ('12') AND
                        S.FIELD1 NOT IN ('755', '750', '754', '752'))) THEN
                  1
                  ELSE
                  0
               END) FAIL_CARD
      FROM XOMORDERITEMS ITEM
      LEFT JOIN XOMORDRELEASE RELEASE ON ITEM.ORDERS_ID =
                                          RELEASE.ORDERS_ID
                                       AND RELEASE.STATUS IN
                                          ('SSUC', 'SFAL')
      LEFT JOIN SHIPMODE SHIPMODE ON ITEM.SHIPMODE_ID =
                                       SHIPMODE.SHIPMODE_ID
      LEFT JOIN XOMADDRESS XADDRESS ON ITEM.ADDRESS_ID =
                                       XADDRESS.ADDRESS_ID
      LEFT JOIN STORE S ON S.STORE_ID = ITEM.STOREENT_ID
      WHERE ITEM.CATENTRY_ID = ITEM.FIELD1
         AND TO_CHAR(ITEM.TIMESHIPPED, 'yyyy-mm') IS NOT NULL
         AND SHIPMODE.CARRIER IS NOT NULL
         AND SUBSTR(XADDRESS.CITY, 1, 2) IS NOT NULL
         AND TO_CHAR(ITEM.TIMESHIPPED, 'yyyy-mm') = '2010-06'
      GROUP BY TO_CHAR(ITEM.TIMESHIPPED, 'yyyy-mm'),
               SHIPMODE.CODE,
               SHIPMODE.CARRIER,
               SUBSTR(XADDRESS.CITY, 1, 2)) T
ORDER BY TO_DATE(T.TIME, 'yyyy-mm'), T.CODE;

我解释下这个查询的思路，他应该是在from后面的一个子查询（使用了group by）作为一个基础集合T，再利用这个T的集合完成上面的主查询显示的值。

但是，由于主查询里面使用了一个nvl函数
   NVL((SELECT NXSC.PRICE
         FROM XSCQUOTATION NXSC
         WHERE NXSC.CODE = T.CODE
            AND NXSC.FIELD1 = T.CITYNAME
            AND NXSC.CATENTRY_TYPE = 'terminal'),
         (SELECT NXSC.PRICE
            FROM XSCQUOTATION NXSC
         WHERE NXSC.CODE = T.CODE
            AND NXSC.FIELD1 = '省外'
            AND NXSC.CATENTRY_TYPE = 'terminal')) * T.SCC_TERMINAL SCC_TERMINAL_PRIICE

来完成if的功能，里面有引用T的结果集进行where的过滤。

我的疑问是，为什么两个子查询会相互影响呢？
我做过下面的尝试：
1.把T做成一个视图，然后再替换掉这个复杂查询里面的T部分，仍是报错。
2.去掉主查询里面的nvl那个显示字段，可以成功。

请问是否这种思路来做这种if的目的是不可能了呢？我想了很久帮他改写都不成功。
恳请各位指点一下，谢谢。