下面是我写的语句,肯定不对,因为语句在oracle中不能执行成功。想请高手给我指出错误,或者能说下写语句的思路,谢谢各位了!
Declare
xing number; --xing为自定义的变量
begin
SELECT case
when SRE>3500 then
if ZYKCDJZE<(SRE-SJHJE-QTMSSD-3500)*0.3 --ZYKCDJZE、SRE、SJHJE、QTMSSD为数据库中的字段
then xing:=ZYKCDJZE
elsif ZYKCDJZE>=(SRE-SJHJE-QTMSSD-3500)*0.3
then xing:=(SRE-SJHJE-QTMSSD-3500)*0.3
end if;
end case;
--------想把xing经过判断给求出来,然后将求出的xing的值,赋给后面的select case 语句的when子句中使用----------
SELECT CASE
WHEN (ynssde-1500+ZYKCDJZE-xing) > 0 AND (ynssde-1500+ZYKCDJZE-xing) <= 1500 THEN
'3%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 1500 AND (ynssde-1500+ZYKCDJZE-xing) <= 4500 THEN
'10%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 4500 AND (ynssde-1500+ZYKCDJZE-xing) <= 9000 THEN
'20%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 9000 AND (ynssde-1500+ZYKCDJZE-xing) <= 35000 THEN
'25%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 35000 AND (ynssde-1500+ZYKCDJZE-xing) <= 55000 THEN
'30%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 55000 AND (ynssde-1500+ZYKCDJZE-xing) <= 80000 THEN
'35%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 80000 THEN
'45%'
ELSE
NULL
END sl,
COUNT(distinct ZZHM) as rs,
SUM(sre) as sre
--SUM(sjynse) as sjynse--
FROM bims.jcxx_nsrsrnsxx2 t
where t.zgswjg_dm like '23502%'
and t.zflx_dm = '0101'
and t.zzlx_dm <> '99'
and t.ffyf = '201104'
group by CASE
WHEN (ynssde-1500+ZYKCDJZE-xing) > 0 AND (ynssde-1500+ZYKCDJZE-xing) <= 1500 THEN
'3%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 1500 AND (ynssde-1500+ZYKCDJZE-xing) <= 4500 THEN
'10%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 4500 AND (ynssde-1500+ZYKCDJZE-xing) <= 9000 THEN
'20%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 9000 AND (ynssde-1500+ZYKCDJZE-xing) <= 35000 THEN
'25%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 35000 AND (ynssde-1500+ZYKCDJZE-xing) <= 55000 THEN
'30%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 55000 AND (ynssde-1500+ZYKCDJZE-xing) <= 80000 THEN
'35%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 80000 THEN
'45%'
ELSE
NULL
END CASE;
END;
Declare
xing number; --xing为自定义的变量
begin
SELECT case
when SRE>3500 then
if ZYKCDJZE<(SRE-SJHJE-QTMSSD-3500)*0.3 --ZYKCDJZE、SRE、SJHJE、QTMSSD为数据库中的字段
then xing:=ZYKCDJZE
elsif ZYKCDJZE>=(SRE-SJHJE-QTMSSD-3500)*0.3
then xing:=(SRE-SJHJE-QTMSSD-3500)*0.3
end if;
end case;
--------想把xing经过判断给求出来,然后将求出的xing的值,赋给后面的select case 语句的when子句中使用----------
SELECT CASE
WHEN (ynssde-1500+ZYKCDJZE-xing) > 0 AND (ynssde-1500+ZYKCDJZE-xing) <= 1500 THEN
'3%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 1500 AND (ynssde-1500+ZYKCDJZE-xing) <= 4500 THEN
'10%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 4500 AND (ynssde-1500+ZYKCDJZE-xing) <= 9000 THEN
'20%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 9000 AND (ynssde-1500+ZYKCDJZE-xing) <= 35000 THEN
'25%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 35000 AND (ynssde-1500+ZYKCDJZE-xing) <= 55000 THEN
'30%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 55000 AND (ynssde-1500+ZYKCDJZE-xing) <= 80000 THEN
'35%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 80000 THEN
'45%'
ELSE
NULL
END sl,
COUNT(distinct ZZHM) as rs,
SUM(sre) as sre
--SUM(sjynse) as sjynse--
FROM bims.jcxx_nsrsrnsxx2 t
where t.zgswjg_dm like '23502%'
and t.zflx_dm = '0101'
and t.zzlx_dm <> '99'
and t.ffyf = '201104'
group by CASE
WHEN (ynssde-1500+ZYKCDJZE-xing) > 0 AND (ynssde-1500+ZYKCDJZE-xing) <= 1500 THEN
'3%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 1500 AND (ynssde-1500+ZYKCDJZE-xing) <= 4500 THEN
'10%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 4500 AND (ynssde-1500+ZYKCDJZE-xing) <= 9000 THEN
'20%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 9000 AND (ynssde-1500+ZYKCDJZE-xing) <= 35000 THEN
'25%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 35000 AND (ynssde-1500+ZYKCDJZE-xing) <= 55000 THEN
'30%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 55000 AND (ynssde-1500+ZYKCDJZE-xing) <= 80000 THEN
'35%'
WHEN (ynssde-1500+ZYKCDJZE-xing) > 80000 THEN
'45%'
ELSE
NULL
END CASE;
END;
when SRE>3500 then
if ZYKCDJZE<(SRE-SJHJE-QTMSSD-3500)*0.3 --ZYKCDJZE、SRE、SJHJE、QTMSSD为数据库中的字段
then xing:=ZYKCDJZE
elsif ZYKCDJZE>=(SRE-SJHJE-QTMSSD-3500)*0.3
then xing:=(SRE-SJHJE-QTMSSD-3500)*0.3
end if;
end case;"应该是这段代码的问题,可以注释该段代码,给变量xing赋值再试试看
when SRE>3500 then
if ZYKCDJZE<(SRE-SJHJE-QTMSSD-3500)*0.3 --ZYKCDJZE、SRE、SJHJE、QTMSSD为数据库中的字段
then xing:=ZYKCDJZE
elsif ZYKCDJZE>=(SRE-SJHJE-QTMSSD-3500)*0.3
then xing:=(SRE-SJHJE-QTMSSD-3500)*0.3
end if;
end case;select ... from ....,这个是select语句的语法.请问你的from和具体的表在那里?
既然没有表,何来表中的字段?
并且select ...case ...end from ...语句中不能有if..then..end if语句。你可能需要先取出表中的字段赋给变量后再判断。使用如下语法:
select 字段1,字段2... into 变量1,变量2... from 表建议看看oracle pl/sql 编程基础的书
when SRE>3500 then
if ZYKCDJZE<(SRE-SJHJE-QTMSSD-3500)*0.3 --ZYKCDJZE、SRE、SJHJE、QTMSSD为数据库中的字段
then xing:=ZYKCDJZE
elsif ZYKCDJZE>=(SRE-SJHJE-QTMSSD-3500)*0.3
then xing:=(SRE-SJHJE-QTMSSD-3500)*0.3
end if;
end case;"
这段代码的问题,因为后面的那段SQL语句把xing变量去掉是可以执行的。
谢谢tangren ,指出的问题所在,很有道理。但是我怎么将数据库中查询出来的字段,经过判断,赋给变量呢?