sql语句的疑问

Question

请大家帮我解决下这个oracle里面sql的问题：
  select (select ComCode from ldcom where trim(ComCode) = substr(a.managecom, 0, 4)) ComCode,(select name from ldcom where trim(ComCode) = substr(a.managecom, 0, 4)) ComCode1,(select name from ldcom where trim(ComCode) = substr(a.managecom, 0, 6)) ComCode2,(select name from ldcom where trim(ComCode) = substr(a.managecom, 0, 8)) ComCode3,(select name from labranchgroup where agentgroup =(select upbranch from labranchgroup where agentgroup =(select upbranch from labranchgroup where agentgroup = a.branchcode))) branchattr1,(select name from labranchgroup where agentgroup = (select upbranch from labranchgroup where agentgroup = a.branchcode)) branchattr2,(select name from labranchgroup where agentgroup = a.branchcode) branchattr3,(select lab.branchattr from labranchgroup lab where lab.agentgroup=a.branchcode) branchattr3code,a.agentcode,a.name,(select gradename from laagentgrade d where d.gradecode =(select agentgrade from latree where agentcode = a.agentcode)) gradename,a.EmployDate,( select PrtNo from view_lccont_lbcont where ContNo = b.contno  ),(select AppntName from view_lccont_lbcont where ContNo = b.contno),b.InsuredName,b.RiskCode,(select RiskName from lmriskapp where RiskCode = b.RiskCode ),b.payYears,nvl(b.amnt,0),(select nvl(Prem,0) from lccont where lccont.contno = b.contno),(select cstandprem(b.riskcode, b.payyears, b.payintv, 1, (select nvl(Prem,0) from lccont where lccont.contno = b.contno)) from dual),(select nvl(Prem,0)from lcfirstpol where lcfirstpol.contno = b.contno),(select cstandprem(lcfirstpol.riskcode, lcfirstpol.payyears, lcfirstpol.payintv, 1, (select nvl(Prem,0)from lcfirstpol where lcfirstpol.contno = b.contno)) from dual),(select nvl(PayMoney,0) from LJTempFee where LJTempFee.TempFeeNo = b.contno),(select cstandprem(lcfirstpol.riskcode, lcfirstpol.payyears, lcfirstpol.payintv, 1, (select nvl(PayMoney,0) from LJTempFee where LJTempFee.TempFeeNo = b.contno)) from dual),1, b.UWDate,'2008-03-02','2008-04-29' ,b.contno from laagent a, lcpol b,labranchgroup c,lccont,lcfirstpol,LJTempFee where a.agentcode =b.agentcode and a.branchtype = '1' and b.uwflag = 'a' and c.agentgroup = a.agentgroup and a.managecom like '86%' and b.UWDate >= '2008-03-02'and b.UWDate <= '2008-04-29' order by a.managecom ,c.branchattr,a.agentcode,b.RiskCode,b.contno这段sql语句执行总提示temp表空间不够，但我把order by语句注释掉后就能查出结果，请教高手这是什么问题啊？

Answer 1

拜托，排下版好吧，否则谁有兴趣看啊，这么长一串
/* Formatted on 2008/04/29 15:05 (Formatter Plus v4.8.8) */
SELECT   (SELECT comcode
            FROM ldcom
           WHERE TRIM (comcode) = SUBSTR (a.managecom, 0, 4)) comcode,
         (SELECT NAME
            FROM ldcom
           WHERE TRIM (comcode) = SUBSTR (a.managecom, 0, 4)) comcode1,
         (SELECT NAME
            FROM ldcom
           WHERE TRIM (comcode) = SUBSTR (a.managecom, 0, 6)) comcode2,
         (SELECT NAME
            FROM ldcom
           WHERE TRIM (comcode) = SUBSTR (a.managecom, 0, 8)) comcode3,
         (SELECT NAME
            FROM labranchgroup
           WHERE agentgroup =
                    (SELECT upbranch
                       FROM labranchgroup
                      WHERE agentgroup =
                               (SELECT upbranch
                                  FROM labranchgroup
                                 WHERE agentgroup = a.branchcode)))
                                                                  branchattr1,
         (SELECT NAME
            FROM labranchgroup
           WHERE agentgroup = (SELECT upbranch
                                 FROM labranchgroup
                                WHERE agentgroup = a.branchcode)) branchattr2,
         (SELECT NAME
            FROM labranchgroup
           WHERE agentgroup = a.branchcode) branchattr3,
         (SELECT lab.branchattr
            FROM labranchgroup lab
           WHERE lab.agentgroup = a.branchcode) branchattr3code, a.agentcode,
         a.NAME,
         (SELECT gradename
            FROM laagentgrade d
           WHERE d.gradecode = (SELECT agentgrade
                                  FROM latree
                                 WHERE agentcode = a.agentcode)) gradename,
         a.employdate, (SELECT prtno
                          FROM view_lccont_lbcont
                         WHERE contno = b.contno), (SELECT appntname
                                                      FROM view_lccont_lbcont
                                                     WHERE contno = b.contno),
         b.insuredname, b.riskcode, (SELECT riskname
                                       FROM lmriskapp
                                      WHERE riskcode = b.riskcode),
         b.payyears, NVL (b.amnt, 0), (SELECT NVL (prem, 0)
                                         FROM lccont
                                        WHERE lccont.contno = b.contno),
         (SELECT cstandprem (b.riskcode,
                             b.payyears,
                             b.payintv,
                             1,
                             (SELECT NVL (prem, 0)
                                FROM lccont
                               WHERE lccont.contno = b.contno)
                            )
            FROM DUAL),
         (SELECT NVL (prem, 0)
            FROM lcfirstpol
           WHERE lcfirstpol.contno = b.contno),
         (SELECT cstandprem (lcfirstpol.riskcode,
                             lcfirstpol.payyears,
                             lcfirstpol.payintv,
                             1,
                             (SELECT NVL (prem, 0)
                                FROM lcfirstpol
                               WHERE lcfirstpol.contno = b.contno)
                            )
            FROM DUAL),
         (SELECT NVL (paymoney, 0)
            FROM ljtempfee
           WHERE ljtempfee.tempfeeno = b.contno),
         (SELECT cstandprem (lcfirstpol.riskcode,
                             lcfirstpol.payyears,
                             lcfirstpol.payintv,
                             1,
                             (SELECT NVL (paymoney, 0)
                                FROM ljtempfee
                               WHERE ljtempfee.tempfeeno = b.contno)
                            )
            FROM DUAL),
         1, b.uwdate, '2008-03-02', '2008-04-29', b.contno
    FROM laagent a, lcpol b, labranchgroup c, lccont, lcfirstpol, ljtempfee
   WHERE a.agentcode = b.agentcode
     AND a.branchtype = '1'
     AND b.uwflag = 'a'
     AND c.agentgroup = a.agentgroup
     AND a.managecom LIKE '86%'
     AND b.uwdate >= '2008-03-02'
     AND b.uwdate <= '2008-04-29'
ORDER BY a.managecom, c.branchattr, a.agentcode, b.riskcode, b.contno

Answer 2

order by是要在temp表空间做排序的,如果temp表空间不够当然抱错.
你只有
1. 增大temp表空间
2. 优化或拆分SQL

Answer 3

2楼的建议很好.
如果实在不能增大TEMP表空间,建议拆分SQL.把几个大的子查询做成临时表的方式或许会有效果.