select nvl(zqdw,'') as zqdw,nvl(xzqh,'') as xzqh ,
count(case when bdsbj='0' then 1 end ) as bdbs,
nvl(sum(case when bdsbj='0' then fkje+znj end),0) as bdje,
nvl(count(case when bdsbj='1' then 1 end ),0) as ydbs,
nvl(sum(case when bdsbj='1' then fkje+znj end ),0) as ydje,
sum(fkje+znj) as hjje from bank_data_settel
where jkrq>=to_date('2009-01-01','yyyy-mm-dd')
and jkrq<=to_date('2009-11-20','yyyy-mm-dd')
and skxh||yhlsh in ( select skxh||yhlsh from app_kdxx where cfdw in ('4106000000','4106030000'))
group by zqdw,xzqh order by zqdw,xzqh 出错。 提示 ora-06502 pl/sql :数字或值错误但是把 ( select skxh||yhlsh from app_kdxx where cfdw in ('4106000000','4106030000'))
这个子查询改为 ( select skxh||yhlsh from app_kdxx )
就没错了,
请问这是为什么,?该如何改?
count(case when bdsbj='0' then 1 end ) as bdbs,
nvl(sum(case when bdsbj='0' then fkje+znj end),0) as bdje,
nvl(count(case when bdsbj='1' then 1 end ),0) as ydbs,
nvl(sum(case when bdsbj='1' then fkje+znj end ),0) as ydje,
sum(fkje+znj) as hjje from bank_data_settel
where jkrq>=to_date('2009-01-01','yyyy-mm-dd')
and jkrq<=to_date('2009-11-20','yyyy-mm-dd')
and skxh||yhlsh in ( select skxh||yhlsh from app_kdxx where cfdw in ('4106000000','4106030000'))
group by zqdw,xzqh order by zqdw,xzqh 出错。 提示 ora-06502 pl/sql :数字或值错误但是把 ( select skxh||yhlsh from app_kdxx where cfdw in ('4106000000','4106030000'))
这个子查询改为 ( select skxh||yhlsh from app_kdxx )
就没错了,
请问这是为什么,?该如何改?
select skxh||yhlsh from app_kdxx where cfdw in ('4106000000','4106030000')是否查无记录呀?
很明显是长度短了。
select skxh||yhlsh from app_kdxx where cfdw in ('4106000000','4106030000')
这一句直接拿出来就能用。
但是放在子查询里就不行了。 是不是 CASE 语句对之查询有什么限制?
、
create table APP_KDXX
(
XZQH VARCHAR2(6) not null,
JDSBH VARCHAR2(12) not null,
ZQMJ VARCHAR2(10),
CFDW VARCHAR2(200),
SKXH VARCHAR2(33) not null,
YHLSH VARCHAR2(15) not null
);create table JJ_DATAUP_SETTEL
(
SKXH VARCHAR2(33) not null,
JDSBH VARCHAR2(15) not null,
JDSLB VARCHAR2(1) not null,
JKR VARCHAR2(50) not null,
FKJE NUMBER(18,2) not null,
ZNJ NUMBER(18,2) not null,
JKRQ DATE not null,
CJRQ DATE not null,
CLND VARCHAR2(10) not null,
YHXTSJ DATE not null,
XZQH VARCHAR2(6) not null,
ZQDW VARCHAR2(120) not null,
DSCZDWBH VARCHAR2(6) not null,
DSCZDWMC VARCHAR2(120) not null,
DSWDBH VARCHAR2(20) not null,
DSWDMC VARCHAR2(120) not null,
YHGYH VARCHAR2(20) not null,
SKYMC VARCHAR2(40) not null,
SCIPDZ VARCHAR2(20),
XZIPDZ VARCHAR2(20),
SFZMHM VARCHAR2(18),
BDSBJ VARCHAR2(1) not null,
CZPJHM VARCHAR2(20) not null,
CXBJ VARCHAR2(1) not null,
YHLSH VARCHAR2(20) not null,
YHDM VARCHAR2(2) not null,
SJHDBJ VARCHAR2(1) default '0' not null,
ZJHDBJ VARCHAR2(1) default '0' not null,
YHZHMC VARCHAR2(120),
YHZH VARCHAR2(30),
KHH VARCHAR2(120)
);----test datainsert into JJ_DATAUP_SETTEL (SKXH, JDSBH, JDSLB, JKR, FKJE, ZNJ, JKRQ, CJRQ, CLND, YHXTSJ, XZQH, ZQDW, DSCZDWBH, DSCZDWMC, DSWDBH, DSWDMC, YHGYH, SKYMC, SCIPDZ, XZIPDZ, SFZMHM, BDSBJ, CZPJHM, CXBJ, YHLSH, YHDM, SJHDBJ, ZJHDBJ, YHZHMC, YHZH, KHH)
values ('410600062009021000050010000189041', '100005006', '1', '付保光', 50, 0, to_date('20-04-2009', 'dd-mm-yyyy'), to_date('16-04-2009', 'dd-mm-yyyy'), '2009', to_date('20-04-2009 09:17:31', 'dd-mm-yyyy hh24:mi:ss'), '410602', '河南省鹤壁市公安交通巡逻警察支队二大队', '410600', '河南省鹤壁市财政局非税收入管理局', '41060006000101', '鹤壁市大湖邮政储蓄', '4106000600010103', '许凡杰', null, null, '41060319780212353X', '0', '7542061', 'C', '1000018904', '06', '0', '0', null, null, null);
insert into JJ_DATAUP_SETTEL (SKXH, JDSBH, JDSLB, JKR, FKJE, ZNJ, JKRQ, CJRQ, CLND, YHXTSJ, XZQH, ZQDW, DSCZDWBH, DSCZDWMC, DSWDBH, DSWDMC, YHGYH, SKYMC, SCIPDZ, XZIPDZ, SFZMHM, BDSBJ, CZPJHM, CXBJ, YHLSH, YHDM, SJHDBJ, ZJHDBJ, YHZHMC, YHZH, KHH)
values ('410600062009001900018610000576341', '190001860', '1', '王小孬', 100, 0, to_date('26-06-2009', 'dd-mm-yyyy'), to_date('26-06-2009', 'dd-mm-yyyy'), '2009', to_date('26-06-2009 16:25:35', 'dd-mm-yyyy hh24:mi:ss'), '410600', '河南省鹤壁市公安交通巡逻警察支队', '410600', '河南省鹤壁市财政局非税收入管理局', '41060006000117', '鹤壁市淇滨邮政储蓄', '4106000600011706', '张海川', null, null, '410611196208273012', '0', '5601219', 'C', '1000057634', '06', '0', '0', null, null, null);
insert into JJ_DATAUP_SETTEL (SKXH, JDSBH, JDSLB, JKR, FKJE, ZNJ, JKRQ, CJRQ, CLND, YHXTSJ, XZQH, ZQDW, DSCZDWBH, DSCZDWMC, DSWDBH, DSWDMC, YHGYH, SKYMC, SCIPDZ, XZIPDZ, SFZMHM, BDSBJ, CZPJHM, CXBJ, YHLSH, YHDM, SJHDBJ, ZJHDBJ, YHZHMC, YHZH, KHH)
values ('410600062009021000000710000225391', '100000076', '1', '靳玉东', 50, 0, to_date('24-04-2009', 'dd-mm-yyyy'), to_date('16-04-2009', 'dd-mm-yyyy'), '2009', to_date('24-04-2009 10:41:26', 'dd-mm-yyyy hh24:mi:ss'), '410602', '河南省鹤壁市公安交通巡逻警察支队二大队', '410600', '河南省鹤壁市财政局非税收入管理局', '41060006000104', '鹤壁市鹤山邮政储蓄', '4106000600010403', '牛丽英', null, null, '410602197010240555', '0', '7540441', 'C', '1000022539', '06', '0', '0', null, null, null);
insert into JJ_DATAUP_SETTEL (SKXH, JDSBH, JDSLB, JKR, FKJE, ZNJ, JKRQ, CJRQ, CLND, YHXTSJ, XZQH, ZQDW, DSCZDWBH, DSCZDWMC, DSWDBH, DSWDMC, YHGYH, SKYMC, SCIPDZ, XZIPDZ, SFZMHM, BDSBJ, CZPJHM, CXBJ, YHLSH, YHDM, SJHDBJ, ZJHDBJ, YHZHMC, YHZH, KHH)
values ('410600062009001900001510000233791', '190000151', '1', '杨志新', 200, 0, to_date('26-04-2009', 'dd-mm-yyyy'), to_date('16-04-2009', 'dd-mm-yyyy'), '2009', to_date('26-04-2009 08:12:16', 'dd-mm-yyyy hh24:mi:ss'), '410600', '河南省鹤壁市公安交通巡逻警察支队', '410600', '河南省鹤壁市财政局非税收入管理局', '41060006000117', '鹤壁市淇滨邮政储蓄', '4106000600011704', '冯洁', null, null, '#', '0', '7541392', 'C', '1000023379', '06', '0', '0', null, null, null);
insert into APP_KDXX (XZQH, JDSBH, ZQMJ, CFDW, SKXH, YHLSH)
values ('410600', '190000151', '999999', '4106000000', '410600062009001900001510000233791', '1000023379');
insert into APP_KDXX (XZQH, JDSBH, ZQMJ, CFDW, SKXH, YHLSH)
values ('410600', '190001860', '999999', '4106000000', '410600062009001900018610000576341', '1000057634');
insert into APP_KDXX (XZQH, JDSBH, ZQMJ, CFDW, SKXH, YHLSH)
values ('410602', '100000076', '047378', '4106020000', '410600062009021000000710000225391', '1000022539');
insert into APP_KDXX (XZQH, JDSBH, ZQMJ, CFDW, SKXH, YHLSH)
values ('410602', '100005006', '047379', '4106020000', '410600062009021000050010000189041', '1000018904');
---
CREATE OR REPLACE PACKAGE SERVER_PACK is
PRAGMA SERIALLY_REUSABLE;
type cur_ref is ref cursor;
procedure total_by_zz(in_jjxzqh varchar2,in_jkrq1 varchar2,in_jkrq2 varchar2,
out_ret out cur_ref);
end server_pack;
CREATE OR REPLACE PACKAGE BODY SERVER_PACK is
PRAGMA SERIALLY_REUSABLE;
procedure total_by_zz(in_jjxzqh varchar2,in_jkrq1 varchar2,in_jkrq2 varchar2,
out_ret out cur_ref)
is var_cnt integer;
var_idx integer;
var_str varchar2(200);
var_xzqh varchar2(200);
type var_array is table of number
index by binary_integer;
myarray var_array;
var_sql varchar2(500);
var_zqdw varchar2(200);
var_temp varchar2(200);
begin
var_str:=in_jjxzqh;
var_idx:=1;
if instr(var_str,'|')=0 then
var_xzqh:=''''||var_str||'''';
else
while instr(var_str,'|')>0 loop
var_cnt:=instr(var_str,'|');
myarray(var_idx):=substr(var_str,1,var_cnt-1);
var_str:=substr(var_str,var_cnt+1,length(var_str));
var_idx:=var_idx+1;
end loop;
myarray(var_idx):=var_str;
for var_cnt in 1..var_idx loop
var_xzqh:=var_xzqh||','||''''||myarray(var_cnt)||'''';
end loop;
var_xzqh:=substr(var_xzqh,2,length(var_xzqh));
end if;
var_xzqh:='('||var_xzqh||')';
var_sql:='select nvl(zqdw,'||''''||''''||') as zqdw,nvl(xzqh,'||''''||''''||') as xzqh ,'||
'count(case when bdsbj='||''''||'0'||''''||' then 1 end ) as bdbs,'||
'sum(case when bdsbj='||''''||'0'||''''||' then fkje+znj end) as bdje,'||
'count(case when bdsbj='||''''||'1'||''''||' then 1 end ) as ydbs,'||
'sum(case when bdsbj='||''''||'1'||''''||' then fkje+znj end ) as ydje,'||
'sum(fkje+znj) as hjje from JJ_DATAUP_SETTEL'||
' where jkrq>=to_date('||''''||in_jkrq1||''''||','||''''||'yyyy-mm-dd'||''''||')'||
' and jkrq<=to_date('||''''||in_jkrq2||''''||','||''''||'yyyy-mm-dd'||''''||')'||
' and skxh||yhlsh in ( select skxh||yhlsh from app_kdxx where cfdw in '||var_xzqh||')'||
' group by zqdw,xzqh order by zqdw,xzqh';
open out_ret for var_sql;end;end server_pack;
---调用过程
excute server_pack.total_by_zz('4106000000|4106020000','2009-01-01','2009-11-20')出错 ora-06502 pl/sql:数字或值错误如果把 上面的动态语句改为 var_sql:='select nvl(zqdw,'||''''||''''||') as zqdw,nvl(xzqh,'||''''||''''||') as xzqh ,'||
'count(case when bdsbj='||''''||'0'||''''||' then 1 end ) as bdbs,'||
'sum(case when bdsbj='||''''||'0'||''''||' then fkje+znj end) as bdje,'||
'count(case when bdsbj='||''''||'1'||''''||' then 1 end ) as ydbs,'||
'sum(case when bdsbj='||''''||'1'||''''||' then fkje+znj end ) as ydje,'||
'sum(fkje+znj) as hjje from JJ_DATAUP_SETTEL'||
' where jkrq>=to_date('||''''||in_jkrq1||''''||','||''''||'yyyy-mm-dd'||''''||')'||
' and jkrq<=to_date('||''''||in_jkrq2||''''||','||''''||'yyyy-mm-dd'||''''||')'||
' and skxh||yhlsh in ( select skxh||yhlsh from app_kdxx )'||
' group by zqdw,xzqh order by zqdw,xzqh';
就能通过??
谁测试下看看!!!!
白白让我在sql语句上找了这么久
谢谢大家了!
怎么会报ora-06502 pl/sql :数字或值错误
牛头不对马嘴啊