select case when 字段<0 then 1 else 字段 end from tabl
select decode(sign(col),-1,0,col) from tab;
select (case when col<0 then 0 else col end) from tablename
V 表示字段 SELECT CASE WHEN V < 0 THEN O WHEN V > 0 THEN V ENDFROM TABLE
谢谢 各位, 我想知道换成函数怎么写 ,然后直接用创建的函数 如 create or replace function abc .....select abc(字段) from table 得到我想要的结果
--函数 CREATE OR REPLACE FUNCTION getnum( num IN NUMBER ) RETURN NUMBER IS result NUMBER; BEGIN SELECT decode(sign(num),-1,0,num) INTO result FROM dual; RETURN result; END;--测试: SELECT getnum(2) FROM dual;SELECT getnum(-2) FROM dual;
--好像没必要用一定要用函数吧 create or replace function abc(v_in number) return number as begin if v_in<0 then return 0; else return v_in; end if; end;
V 表示字段
SELECT CASE
WHEN V < 0 THEN O
WHEN V > 0 THEN V
ENDFROM TABLE
如 create or replace function abc .....select abc(字段) from table 得到我想要的结果
CREATE OR REPLACE FUNCTION getnum( num IN NUMBER )
RETURN NUMBER
IS
result NUMBER;
BEGIN
SELECT decode(sign(num),-1,0,num) INTO result FROM dual;
RETURN result;
END;--测试:
SELECT getnum(2) FROM dual;SELECT getnum(-2) FROM dual;
create or replace function abc(v_in number)
return number
as
begin
if v_in<0 then
return 0;
else return v_in;
end if;
end;