表A 记录在100万以上,B 30万以上,C 30万以上,D 2万以上。
有参数firstday、lastday 日期型,c1 varchar2。
下面的语句能在pl/sql developer,pb8,ado.net中运行,有占慢。
select A.a,B.a,B.b,B.c,C.a,
sum(case when (A.xyq_z<=firstday or A.yxq_z is null) then lastday else A.yxq_z end - case when A.yxq_q<=lastday then firstday else A.yxq_q end)+1 as ts,sum(D.je)
from A A,B B,C C,D D
where ......
group by A.a,B.a,B.c,C.a
having sum(D.je)>0
原因是:某人在某月不是连续经营,而我是要按经营天数算。
例:ID号(即上面的A.a) 状态 生效日期 有效期起 有效期止
123467 开业 2004-01-01 2004-01-01 2004-01-15
123467 停业 2004-01-16 2004-01-16 2004-01-20
123467 复业 2004-01-21 2004-01-21 2004-01-31由于慢,我对其进行了改写,改写后在pb8中运行良好,速度提高了约几倍,但在pl/sql developer 中编译不能通过,错误提示附后。select B.a,B.b,B.c,C.a,D.je,e.ts
from B B,C C,D D,(select A.a,
sum(case when (A.xyq_z<=firstday or A.yxq_z is null) then lastday else A.yxq_z end - case when A.yxq_q<=lastday then firstday else A.yxq_q end)+1 from A E
where ......
group by E.a) as ts
where ...... and B.a=E.a and D.je>0错误:
Compilation errors for FUNCTION playboy.CSK_F_YSBTJ
Error: PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( - + all mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current distinct max min prior sql stddev sum unique
variance execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
Line: 34
有参数firstday、lastday 日期型,c1 varchar2。
下面的语句能在pl/sql developer,pb8,ado.net中运行,有占慢。
select A.a,B.a,B.b,B.c,C.a,
sum(case when (A.xyq_z<=firstday or A.yxq_z is null) then lastday else A.yxq_z end - case when A.yxq_q<=lastday then firstday else A.yxq_q end)+1 as ts,sum(D.je)
from A A,B B,C C,D D
where ......
group by A.a,B.a,B.c,C.a
having sum(D.je)>0
原因是:某人在某月不是连续经营,而我是要按经营天数算。
例:ID号(即上面的A.a) 状态 生效日期 有效期起 有效期止
123467 开业 2004-01-01 2004-01-01 2004-01-15
123467 停业 2004-01-16 2004-01-16 2004-01-20
123467 复业 2004-01-21 2004-01-21 2004-01-31由于慢,我对其进行了改写,改写后在pb8中运行良好,速度提高了约几倍,但在pl/sql developer 中编译不能通过,错误提示附后。select B.a,B.b,B.c,C.a,D.je,e.ts
from B B,C C,D D,(select A.a,
sum(case when (A.xyq_z<=firstday or A.yxq_z is null) then lastday else A.yxq_z end - case when A.yxq_q<=lastday then firstday else A.yxq_q end)+1 from A E
where ......
group by E.a) as ts
where ...... and B.a=E.a and D.je>0错误:
Compilation errors for FUNCTION playboy.CSK_F_YSBTJ
Error: PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( - + all mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current distinct max min prior sql stddev sum unique
variance execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
Line: 34
select B.a,B.b,B.c,C.a,D.je,e.ts
from B B,C C,D D,(select A.a,
sum(case when (A.xyq_z<=firstday or A.yxq_z is null) then lastday else A.yxq_z end - case when A.yxq_q<=lastday then firstday else A.yxq_q end)+1 ts from A
where ......
group by A.a) E
where ...... and B.a=E.a and D.je>0
from B B,C C,D D,(select A.a,
sum(decode(sign(A.xyq_z-firstday),-1,lastday,0,lastday,A.yxq_z) - decode(sign(A.yxq_q-lastday),-1,firstday,0,firstday,A.yxq_q))+sum(decode(A.yxq_z,null,lastday))+1 from A E
where ......
group by E.a) as ts
where ...... and B.a=E.a and D.je>0
你的思路我已开导了我,但你的语句是无法通过编译的,我已改为了:
select a.a,sum(decode(sign(nvl(yxq_z,lastday)-yxq_z),1,lastday,yxq_z)
-decode(sign(yxq_q-firstday),-1,firstday,yxq-q))
from a
where ......
group by a.a ;
已通过编译,其结果是正确的,非常感谢!