ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 AAA.TYPESTT
Schema为AAA
执行的SQL语句为
SQL code?123 SELECT ID,STT,grouping(ID),grouping(STT) FROM TABLE (CORP('sys1','code1')) group by grouping sets((ID),(STT)); 毕业论文
错误信息为
SQL code?1 ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 AAA.TYPESTT
自定义类型为
SQL code?123 CREATE OR REPLACE TYPE "AAA"."TYPESTT" AS OBJECT( ID VARCHAR2 (30), STT VARCHAR (10))
SQL code?1 CREATE OR REPLACE TYPE "AAA"."CORPSTT" AS TABLE OF TYPESTT
CORP函数的定义如下
SQL code?1234567891011121314151617181920212223242526272829303132 CREATEORREPLACEFUNCTION "AAA"."CORP"(SYS1 VARCHAR , CODE1 VARCHAR) RETURN CORPSTT PIPELINED AS V1 TYPESTT ; VCODE VARCHAR(30); VSYS VARCHAR(10); V_DATE VARCHAR(10); BEGIN VCODE := CODE1 ; VSYS := SYS1 ; V_DATE := TO_CHAR(SYSDATE,'YYYY-MM-DD') ; FOR MYROW IN( SELECT ID , STT FROM RLCP UNION ALL SELECT CORPID , CORPROLE FROM RRCP ) LOOP V1 := TYPESTT(MYROW.ID, MYROW.STT); PIPE ROW (V1); END LOOP; RETURN ; END ;
GROUPING不能在grouping sets使用，只能在ROLLUP或CUBE的查询中使用。
把grouping sets 换成rollup或者cube应该没问题。
with t as (
select 1 id, '服装' ptype, '男装' stype, 78 amount, '2009-06-25' saledate from dual union all
select 2 id, '服装' ptype, '男装' stype, 33 amount, '2009-03-21' saledate from dual union all
select 3 id, '服装' ptype, '男装' stype, 22 amount, '2009-12-17' saledate from dual union all
select 4 id, '服装' ptype, '内衣' stype, 46 amount, '2009-06-25' saledate from dual union all
select 5 id, '服装' ptype, '内衣' stype, 15 amount, '2009-01-26' saledate from dual union all
select 6 id, '服装' ptype, '内衣' stype, 93 amount, '2009-03-13' saledate from dual
)
select id,ptype,stype,sum(amount) samount ,grouping(id) gid,grouping(stype) gs,grouping(ptype) gp from t
group by grouping sets((ptype,stype,id),(ptype,stype),(ptype))