with问题

Question

--表结构
create table BID_MATCH_ACNT
(
  SETID  NUMBER(6) not null,
  YEAR   NUMBER(4) not null,
  BID    VARCHAR2(20) not null,
  ACNTID VARCHAR2(30),
  FDISP  VARCHAR2(2) default '否'
)
/
create table FINISHEDBILL
(
  SETID       NUMBER(6) not null,
  YEAR        NUMBER(4) not null,
  GUID        VARCHAR2(32) not null,
  ACCOUNTTYPE VARCHAR2(20) not null,
  VOUCHERTYPE VARCHAR2(20) not null,
  BILLSOURCE  VARCHAR2(30) not null,
  BILLTABLE   VARCHAR2(30) default ' ',
  BILLNO      NUMBER(10) default 0,
  FTERM       VARCHAR2(20) default ' ',
  VOUCHERNO   VARCHAR2(20) default ' ',
  SELFDEFINE1 VARCHAR2(50) default ' ',
  SELFDEFINE2 VARCHAR2(50) default ' ',
  SELFDEFINE3 VARCHAR2(100) default ' ',
  SID         VARCHAR2(20) default ' ',
  SNAME       VARCHAR2(20) default ' ',
  SELFDEFINE4 INTEGER default 0,
  DISPNO      NUMBER(10) default 0,
  PID         VARCHAR2(30) default ' ',
  VA01        VARCHAR2(30),
  VA02        VARCHAR2(20),
  VA03        VARCHAR2(1) default '0',
  NA01        NUMBER(20,4)
)
/
create global temporary table GLPER
(
  SETID         NUMBER(6) default 1 not null,
  YEAR          NUMBER(4) default 1900 not null,
  FCODE         VARCHAR2(20) default ' ' not null,
  FDESC         VARCHAR2(100) default ' ',
  FADDR         VARCHAR2(40) default ' ',
  FTEL          VARCHAR2(18) default ' ',
  FPOST         VARCHAR2(6) default ' ',
  FMEMO         VARCHAR2(40) default ' ',
  FPERCOUNT     NUMBER(5) default 0,
  FWORKPERCOUNT NUMBER(5) default 0,
  FCONAREA      NUMBER(10,4) default 0,
  FPERIDCARD    VARCHAR2(50) default ' '
)
/
create table GLVCH
(
  SETID       NUMBER(6) default 1 not null,
  YEAR        NUMBER(4) default 1900 not null,
  FTERM       VARCHAR2(7) default ' ' not null,
  FNO         VARCHAR2(8) default ' ' not null,
  FSEQ        NUMBER(10) default 1 not null,
  FDAY        VARCHAR2(2) default ' ',
  FATT        NUMBER(5) default 0,
  FCODE       VARCHAR2(30) default ' ',
  FDIG        VARCHAR2(80) default ' ',
  FMD         NUMBER(20,4) default 0,
  FMC         NUMBER(20,4) default 0,
  FCY         VARCHAR2(4) default ' ',
  FSCY        NUMBER(20,4) default 0,
  FRATE       NUMBER(10,4) default 0,
  FBSNCO      VARCHAR2(20) default ' ',
  FCUSCO      VARCHAR2(20) default ' ',
  FPERCO      VARCHAR2(20) default ' ',
  FDPTCO      VARCHAR2(20) default ' ',
  FITMCO      VARCHAR2(20) default ' ',
  FPRDCO      VARCHAR2(20) default ' ',
  FPRJCO      VARCHAR2(20) default ' ',
  FQTY        NUMBER(20,4) default 0,
  FPRICE      NUMBER(20,4) default 0,
  FMAKER      VARCHAR2(20) default ' ',
  FCHKER      VARCHAR2(20) default ' ',
  FPOSTER     VARCHAR2(20) default ' ',
  FSYS        VARCHAR2(2) default ' ',
  FLPOST      VARCHAR2(1) default ' ',
  QUOTAID     NUMBER(10) default 0,
  SOURCETYPE  NUMBER(10) default 0,
  FSETTLETYPE VARCHAR2(20) default ' ',
  FBILLNO     VARCHAR2(10) default ' ',
  FBILLDATE   VARCHAR2(10) default ' '
)--语句
WITH T1 AS (SELECT 2 NTYPE,T1.FCODE,T1.FDESC, CASE INSTR(T1.FCODE,'1') WHEN 1 THEN sum(FMC-FMD) ELSE sum(FMD-FMC) END BYSJ,0 LJSJ
FROM GLPER T1 
LEFT JOIN GLVCH T2 ON T2.SETID=1 AND T2.YEAR=2008 AND FTERM='2008.08' AND T2.FPERCO<>' '  AND INSTR(T2.FPERCO,T1.FCODE)=1 AND INSTR(T2.FPERCO,'2')=1 AND T2.FCODE='501' 
LEFT JOIN FINISHEDBILL T3 ON T2.SETID=T3.SETID AND T2.YEAR=T3.YEAR AND T2.FTERM=T3.FTERM AND T2.FNO=T3.VOUCHERNO AND T2.FSEQ=T3.SELFDEFINE4 AND T3.VA03>='1'
WHERE 1=1 
AND INSTR(T1.FCODE,'2')=1 
AND EXISTS ( SELECT SETID FROM BID_MATCH_ACNT T2 WHERE T2.SETID=1 AND T2.YEAR=2008 AND INSTR(T2.BID,T1.FCODE)=1 AND T2.FDISP='是' AND T2.ACNTID='501' ) 
AND T3.SELFDEFINE4 IS NULL
GROUP BY T1.FCODE,T1.FDESC
)
SELECT a.NTYPE,a.FCODE,a.FDESC,a.BYSJ,a.LJSJ FROM T1 a
UNION ALL
SELECT b.NTYPE,' ' fcode,'合计' fdesc,SUM(b.BYSJ) BYSJ,SUM(b.LJSJ) LJSJ FROM T1 b
WHERE LENGTH(b.FCODE)=3  GROUP BY b.NTYPE 
/
可以不用数据就可测试，在pl/sql里运行后提示
ora-00604  递归sql层1出现错误    
ora-00904  "from$_subquery$_003"."setid_12_12" 无效的标识符

Answer 1

--换成这样不就行咯 ... 
SELECT A.NTYPE, A.FCODE, A.FDESC, A.BYSJ, A.LJSJ
  FROM (SELECT 2 NTYPE,
               T1.FCODE,
               T1.FDESC,
               CASE INSTR(T1.FCODE, '1')
                 WHEN 1 THEN
                  SUM(FMC - FMD)
                 ELSE
                  SUM(FMD - FMC)
               END BYSJ,
               0 LJSJ
          FROM GLPER T1
          LEFT JOIN GLVCH T2 ON T2.SETID = 1
                            AND T2.YEAR = 2008
                            AND FTERM = '2008.08'
                            AND T2.FPERCO <> ' '
                            AND INSTR(T2.FPERCO, T1.FCODE) = 1
                            AND INSTR(T2.FPERCO, '2') = 1
                            AND T2.FCODE = '501'
          LEFT JOIN FINISHEDBILL T3 ON T2.SETID = T3.SETID
                                   AND T2.YEAR = T3.YEAR
                                   AND T2.FTERM = T3.FTERM
                                   AND T2.FNO = T3.VOUCHERNO
                                   AND T2.FSEQ = T3.SELFDEFINE4
                                   AND T3.VA03 >= '1'
         WHERE 1 = 1
           AND INSTR(T1.FCODE, '2') = 1
           AND EXISTS (SELECT SETID
                  FROM BID_MATCH_ACNT T2
                 WHERE T2.SETID = 1
                   AND T2.YEAR = 2008
                   AND INSTR(T2.BID, T1.FCODE) = 1
                   AND T2.FDISP = '是'
                   AND T2.ACNTID = '501')
           AND T3.SELFDEFINE4 IS NULL
         GROUP BY T1.FCODE, T1.FDESC) T1 A
UNION ALL
SELECT B.NTYPE, ' ' FCODE, '合计' FDESC, SUM(B.BYSJ) BYSJ, SUM(B.LJSJ) LJSJ
  FROM (SELECT 2 NTYPE,
               T1.FCODE,
               T1.FDESC,
               CASE INSTR(T1.FCODE, '1')
                 WHEN 1 THEN
                  SUM(FMC - FMD)
                 ELSE
                  SUM(FMD - FMC)
               END BYSJ,
               0 LJSJ
          FROM GLPER T1
          LEFT JOIN GLVCH T2 ON T2.SETID = 1
                            AND T2.YEAR = 2008
                            AND FTERM = '2008.08'
                            AND T2.FPERCO <> ' '
                            AND INSTR(T2.FPERCO, T1.FCODE) = 1
                            AND INSTR(T2.FPERCO, '2') = 1
                            AND T2.FCODE = '501'
          LEFT JOIN FINISHEDBILL T3 ON T2.SETID = T3.SETID
                                   AND T2.YEAR = T3.YEAR
                                   AND T2.FTERM = T3.FTERM
                                   AND T2.FNO = T3.VOUCHERNO
                                   AND T2.FSEQ = T3.SELFDEFINE4
                                   AND T3.VA03 >= '1'
         WHERE 1 = 1
           AND INSTR(T1.FCODE, '2') = 1
           AND EXISTS (SELECT SETID
                  FROM BID_MATCH_ACNT T2
                 WHERE T2.SETID = 1
                   AND T2.YEAR = 2008
                   AND INSTR(T2.BID, T1.FCODE) = 1
                   AND T2.FDISP = '是'
                   AND T2.ACNTID = '501')
           AND T3.SELFDEFINE4 IS NULL
         GROUP BY T1.FCODE, T1.FDESC) T1 B
 WHERE LENGTH(B.FCODE) = 3
 GROUP BY B.NTYPE;

Answer 2

SQL> WITH T1 AS (
  2    SELECT 2 NTYPE,T1.FCODE,T1.FDESC, CASE INSTR(T1.FCODE,'1') WHEN 1 THEN sum(FMC-FMD) ELSE sum(FMD-FMC) END BYSJ,0 LJSJ
  3    FROM GLPER T1
  4    LEFT JOIN GLVCH T2 ON T2.SETID=1 AND T2.YEAR=2008 AND FTERM='2008.08' AND T2.FPERCO <>' '  AND INSTR(T2.FPERCO,T1.FCODE)=1 AND INSTR(T2.FPERCO,'2')=1 AND T2.FCODE='501'
  5    LEFT JOIN FINISHEDBILL T3 ON T2.SETID=T3.SETID AND T2.YEAR=T3.YEAR AND T2.FTERM=T3.FTERM AND T2.FNO=T3.VOUCHERNO AND T2.FSEQ=T3.SELFDEFINE4 AND T3.VA03>='1'
  6    WHERE 1=1 AND INSTR(T1.FCODE,'2')=1
  7    AND EXISTS ( SELECT SETID FROM BID_MATCH_ACNT T2 WHERE T2.SETID=1 AND T2.YEAR=2008 AND INSTR(T2.BID,T1.FCODE)=1 AND T2.FDISP='是' AND T2.ACNTID='501' )
  8    AND T3.SELFDEFINE4 IS NULL
  9    GROUP BY T1.FCODE,T1.FDESC
 10  ),
 11  T2 AS (
 12    SELECT 2 NTYPE,T1.FCODE,T1.FDESC, CASE INSTR(T1.FCODE,'1') WHEN 1 THEN sum(FMC-FMD) ELSE sum(FMD-FMC) END BYSJ,0 LJSJ
 13    FROM GLPER T1
 14    LEFT JOIN GLVCH T2 ON T2.SETID=1 AND T2.YEAR=2008 AND FTERM='2008.08' AND T2.FPERCO <>' '  AND INSTR(T2.FPERCO,T1.FCODE)=1 AND INSTR(T2.FPERCO,'2')=1 AND T2.FCODE='501'
 15    LEFT JOIN FINISHEDBILL T3 ON T2.SETID=T3.SETID AND T2.YEAR=T3.YEAR AND T2.FTERM=T3.FTERM AND T2.FNO=T3.VOUCHERNO AND T2.FSEQ=T3.SELFDEFINE4 AND T3.VA03>='1'
 16    WHERE 1=1 AND INSTR(T1.FCODE,'2')=1
 17    AND EXISTS ( SELECT SETID FROM BID_MATCH_ACNT T2 WHERE T2.SETID=1 AND T2.YEAR=2008 AND INSTR(T2.BID,T1.FCODE)=1 AND T2.FDISP='是' AND T2.ACNTID='501' )
 18    AND T3.SELFDEFINE4 IS NULL
 19    GROUP BY T1.FCODE,T1.FDESC
 20  )
 21  SELECT a.NTYPE,a.FCODE,a.FDESC,a.BYSJ,a.LJSJ FROM T1 a
 22  UNION ALL
 23  SELECT b.NTYPE,' ' fcode,'合计' fdesc,SUM(b.BYSJ) BYSJ,SUM(b.LJSJ) LJSJ FROM T2 b
 24  WHERE LENGTH(b.FCODE)=3  GROUP BY b.NTYPE
 25  ;     NTYPE FCODE                FDESC                                                                                  BYSJ       LJSJ
---------- -------------------- -------------------------------------------------------------------------------- ---------- ----------

Answer 3

之所以要使用with，就是想为了只写一次，如果写两次，当然是有很多办法可以解决了。
看上去，你这样写，似乎是with t1 as，这个t1不能在下面写两次，但事实上不是的
是可以在两个以上的select里面的
而且，我以前执行上面的语句是可以通过的，只是现在执行时，他报错，我还没有找出来是啥原因
我用的是
Oracle9i Enterprise Edition Release 9.2.0.1.0 

Answer 4

SELECT NTYPE,
       T1.FCODE,
       T1.FDESC,
       CASE INSTR(T1.FCODE,'1') WHEN 1 THEN sum(FMC-FMD) ELSE sum(FMD-FMC) END BYSJ,
       LJSJ 
       FROM GLPER T1 
       LEFT JOIN GLVCH T2 ON T2.SETID=1 
            AND T2.YEAR=2008 
            AND FTERM='2008.08' 
            AND T2.FPERCO <>' ' 
            AND INSTR(T2.FPERCO,T1.FCODE)=1 
            AND INSTR(T2.FPERCO,'2')=1 
            AND T2.FCODE='501' 
       LEFT JOIN FINISHEDBILL T3 ON T2.SETID=T3.SETID 
            AND T2.YEAR=T3.YEAR 
            AND T2.FTERM=T3.FTERM 
            AND T2.FNO=T3.VOUCHERNO 
            AND T2.FSEQ=T3.SELFDEFINE4 
            AND T3.VA03>='1' 
       WHERE INSTR(T1.FCODE,'2')=1 
          AND EXISTS (SELECT SETID FROM BID_MATCH_ACNT T2 
                             WHERE T2.SETID=1 
                                   AND T2.YEAR=2008 
                                   AND INSTR(T2.BID,T1.FCODE)=1
                                   AND T2.FDISP='是' 
                                   AND T2.ACNTID='501') 
          AND T3.SELFDEFINE4 IS NULL 
   GROUP BY T1.FCODE,T1.FDESC 
   union all (SELECT 1 from  
       (SELECT b.NTYPE,' ' fcode,'合计' fdesc,SUM(b.BYSJ) BYSJ,SUM(b.LJSJ) LJSJ 
               FROM GLPER b 
               WHERE LENGTH(b.FCODE)=3  
        GROUP BY b.NTYPE);

Answer 5

WITH T1 AS (SELECT NTYPE,
       T1.FCODE,
       T1.FDESC,
       CASE INSTR(T1.FCODE,'1') WHEN 1 THEN sum(FMC-FMD) ELSE sum(FMD-FMC) END BYSJ,
       LJSJ 
       FROM GLPER T1 
       LEFT JOIN GLVCH T2 ON T2.SETID=1 
            AND T2.YEAR=2008 
            AND FTERM='2008.08' 
            AND T2.FPERCO <>' ' 
            AND INSTR(T2.FPERCO,T1.FCODE)=1 
            AND INSTR(T2.FPERCO,'2')=1 
            AND T2.FCODE='501' 
       LEFT JOIN FINISHEDBILL T3 ON T2.SETID=T3.SETID 
            AND T2.YEAR=T3.YEAR 
            AND T2.FTERM=T3.FTERM 
            AND T2.FNO=T3.VOUCHERNO 
            AND T2.FSEQ=T3.SELFDEFINE4 
            AND T3.VA03>='1' 
       WHERE INSTR(T1.FCODE,'2')=1 
          AND EXISTS (SELECT SETID FROM BID_MATCH_ACNT T2 
                             WHERE T2.SETID=1 
                                   AND T2.YEAR=2008 
                                   AND INSTR(T2.BID,T1.FCODE)=1
                                   AND T2.FDISP='是' 
                                   AND T2.ACNTID='501') 
          AND T3.SELFDEFINE4 IS NULL 
   GROUP BY T1.FCODE,T1.FDESC
   ) 
   union all (SELECT 1 from  
       (SELECT b.NTYPE,' ' fcode,'合计' fdesc,SUM(b.BYSJ) BYSJ,SUM(b.LJSJ) LJSJ 
               FROM t1 b 
               WHERE LENGTH(b.FCODE)=3  
        GROUP BY b.NTYPE)
        );

Answer 6

BlueskyWide ，似乎你写错了吧

Answer 7

谢谢各位的热情帮助
我装了个10g，直接运行就可以了
难道这是9i的bug？？