select b.x, case when b.x>=0 and b.x<10 then a.t1 when b.x>=10 and b.x<100 then a.t2 when b.x>=100 then a.t3 else '异常' end 范围 from xtable b,fwtable a
这个有点难度, 你字段里的条件格式是否固定? 要用到动态SQL
能详细点吗?怎么用到动态SQL?
写了函数进行测试,其中WITH a AS (SELECT '10>x>=0' t_1, '100>x>=10' t_2, 'x>=100' t_3 FROM DUAL)这段是写测试数据/* Formatted on 2008/05/16 21:15 (Formatter Plus v4.8.8) */ CREATE OR REPLACE FUNCTION test_a (input_x VARCHAR2) RETURN VARCHAR2 IS RESULT VARCHAR2 (100); result_1 VARCHAR2 (100); result_2 VARCHAR2 (100); l_st1 VARCHAR2 (100); l_st2 VARCHAR2 (100); l_st3 VARCHAR2 (100); l_st4 VARCHAR2 (100); l_st5 VARCHAR2 (100); l_st6 VARCHAR2 (100); now_x VARCHAR2 (100); BEGIN now_x := SUBSTR (input_x, INSTR (input_x, '=') + 1); WITH a AS (SELECT '10>x>=0' t_1, '100>x>=10' t_2, 'x>=100' t_3 FROM DUAL) SELECT SUBSTR (t_1, 1, INSTR (t_1, 'x')), SUBSTR (t_1, INSTR (t_1, 'x')), SUBSTR (t_2, 1, INSTR (t_2, 'x')), SUBSTR (t_2, INSTR (t_2, 'x')), SUBSTR (t_3, 1, INSTR (t_3, 'x')), SUBSTR (t_3, INSTR (t_3, 'x')) INTO l_st1, l_st2, l_st3, l_st4, l_st5, l_st6 FROM a; IF (LENGTH (l_st1) < 2) THEN IF (LENGTH (l_st2) < 2) THEN l_st1 := '1=1'; ELSE l_st1 := l_st2; END IF; END IF; IF (LENGTH (l_st2) < 2) THEN l_st2 := l_st1; END IF; IF (l_st1 <> '1=1') THEN l_st1 := REPLACE (l_st1, 'x', now_x); END IF; IF (l_st2 <> '1=1') THEN l_st2 := REPLACE (l_st2, 'x', now_x); END IF; IF (LENGTH (l_st3) < 2) THEN IF (LENGTH (l_st4) < 2) THEN l_st3 := '1=1'; ELSE l_st3 := l_st4; END IF; END IF; IF (LENGTH (l_st4) < 2) THEN l_st4 := l_st3; END IF; IF (l_st3 <> '1=1') THEN l_st3 := REPLACE (l_st3, 'x', now_x); END IF; IF (l_st4 <> '1=1') THEN l_st4 := REPLACE (l_st4, 'x', now_x); END IF; IF (LENGTH (l_st5) < 2) THEN IF (LENGTH (l_st6) < 2) THEN l_st5 := '1=1'; ELSE l_st5 := l_st6; END IF; END IF; IF (LENGTH (l_st6) < 2) THEN l_st6 := l_st5; END IF; IF (l_st5 <> '1=1') THEN l_st5 := REPLACE (l_st5, 'x', now_x); END IF; IF (l_st6 <> '1=1') THEN l_st6 := REPLACE (l_st6, 'x', now_x); END IF; EXECUTE IMMEDIATE 'select case when ' || l_st1 || ' and ' || l_st2 || ' then ''1'' else ''0'' end from dual' INTO RESULT; EXECUTE IMMEDIATE 'select case when ' || l_st3 || ' and ' || l_st4 || ' then ''1'' else ''0'' end from dual' INTO result_1; EXECUTE IMMEDIATE 'select case when ' || l_st5 || ' and ' || l_st6 || ' then ''1'' else ''0'' end from dual' INTO result_2; RESULT := RESULT || result_1 || result_2; RETURN RESULT; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- Consider logging the error and then re-raise RAISE; END test_a; /运行结果 select test_a('x=99') from dual 010 表示只有第二个条件满足 思路可以借你参考 假设你的条件表有多个参数 比如 表a id t_1 t_2 t_3 1 10>x>=0 100>x>=10 x>100 2 9<x<=99 x>100 x<90那你可把函数的传入参数改为 input_x verchar2,in_id numberSELECT SUBSTR (t_1, 1, INSTR (t_1, 'x')), SUBSTR (t_1, INSTR (t_1, 'x')), SUBSTR (t_2, 1, INSTR (t_2, 'x')), SUBSTR (t_2, INSTR (t_2, 'x')), SUBSTR (t_3, 1, INSTR (t_3, 'x')), SUBSTR (t_3, INSTR (t_3, 'x')) INTO l_st1, l_st2, l_st3, l_st4, l_st5, l_st6 FROM a; 这句再增加个条件 where id=in_id 这样保证只出来一个结果在调用函数时 select a.*,test_a('x=99',a.id) from a
decode(substr(to_char(100/100,'fm0.09999999'),1,1),'0','字段2','字段3')
) from dual
select b.x,
case
when b.x>=0 and b.x<10 then a.t1
when b.x>=10 and b.x<100 then a.t2
when b.x>=100 then a.t3
else '异常'
end 范围
from xtable b,fwtable a
你字段里的条件格式是否固定?
要用到动态SQL
(SELECT '10>x>=0' t_1, '100>x>=10' t_2, 'x>=100' t_3
FROM DUAL)这段是写测试数据/* Formatted on 2008/05/16 21:15 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION test_a (input_x VARCHAR2)
RETURN VARCHAR2
IS
RESULT VARCHAR2 (100);
result_1 VARCHAR2 (100);
result_2 VARCHAR2 (100);
l_st1 VARCHAR2 (100);
l_st2 VARCHAR2 (100);
l_st3 VARCHAR2 (100);
l_st4 VARCHAR2 (100);
l_st5 VARCHAR2 (100);
l_st6 VARCHAR2 (100);
now_x VARCHAR2 (100);
BEGIN
now_x := SUBSTR (input_x, INSTR (input_x, '=') + 1); WITH a AS
(SELECT '10>x>=0' t_1, '100>x>=10' t_2, 'x>=100' t_3
FROM DUAL)
SELECT SUBSTR (t_1, 1, INSTR (t_1, 'x')), SUBSTR (t_1, INSTR (t_1, 'x')),
SUBSTR (t_2, 1, INSTR (t_2, 'x')), SUBSTR (t_2, INSTR (t_2, 'x')),
SUBSTR (t_3, 1, INSTR (t_3, 'x')), SUBSTR (t_3, INSTR (t_3, 'x'))
INTO l_st1, l_st2,
l_st3, l_st4,
l_st5, l_st6
FROM a; IF (LENGTH (l_st1) < 2)
THEN
IF (LENGTH (l_st2) < 2)
THEN
l_st1 := '1=1';
ELSE
l_st1 := l_st2;
END IF;
END IF; IF (LENGTH (l_st2) < 2)
THEN
l_st2 := l_st1;
END IF; IF (l_st1 <> '1=1')
THEN
l_st1 := REPLACE (l_st1, 'x', now_x);
END IF; IF (l_st2 <> '1=1')
THEN
l_st2 := REPLACE (l_st2, 'x', now_x);
END IF; IF (LENGTH (l_st3) < 2)
THEN
IF (LENGTH (l_st4) < 2)
THEN
l_st3 := '1=1';
ELSE
l_st3 := l_st4;
END IF;
END IF; IF (LENGTH (l_st4) < 2)
THEN
l_st4 := l_st3;
END IF; IF (l_st3 <> '1=1')
THEN
l_st3 := REPLACE (l_st3, 'x', now_x);
END IF; IF (l_st4 <> '1=1')
THEN
l_st4 := REPLACE (l_st4, 'x', now_x);
END IF; IF (LENGTH (l_st5) < 2)
THEN
IF (LENGTH (l_st6) < 2)
THEN
l_st5 := '1=1';
ELSE
l_st5 := l_st6;
END IF;
END IF; IF (LENGTH (l_st6) < 2)
THEN
l_st6 := l_st5;
END IF; IF (l_st5 <> '1=1')
THEN
l_st5 := REPLACE (l_st5, 'x', now_x);
END IF; IF (l_st6 <> '1=1')
THEN
l_st6 := REPLACE (l_st6, 'x', now_x);
END IF; EXECUTE IMMEDIATE 'select case when '
|| l_st1
|| ' and '
|| l_st2
|| ' then ''1'' else ''0'' end from dual'
INTO RESULT; EXECUTE IMMEDIATE 'select case when '
|| l_st3
|| ' and '
|| l_st4
|| ' then ''1'' else ''0'' end from dual'
INTO result_1; EXECUTE IMMEDIATE 'select case when '
|| l_st5
|| ' and '
|| l_st6
|| ' then ''1'' else ''0'' end from dual'
INTO result_2; RESULT := RESULT || result_1 || result_2;
RETURN RESULT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END test_a;
/运行结果
select test_a('x=99') from dual
010
表示只有第二个条件满足
思路可以借你参考
假设你的条件表有多个参数
比如
表a
id t_1 t_2 t_3
1 10>x>=0 100>x>=10 x>100
2 9<x<=99 x>100 x<90那你可把函数的传入参数改为
input_x verchar2,in_id numberSELECT SUBSTR (t_1, 1, INSTR (t_1, 'x')), SUBSTR (t_1, INSTR (t_1, 'x')),
SUBSTR (t_2, 1, INSTR (t_2, 'x')), SUBSTR (t_2, INSTR (t_2, 'x')),
SUBSTR (t_3, 1, INSTR (t_3, 'x')), SUBSTR (t_3, INSTR (t_3, 'x'))
INTO l_st1, l_st2,
l_st3, l_st4,
l_st5, l_st6
FROM a;
这句再增加个条件
where id=in_id
这样保证只出来一个结果在调用函数时
select a.*,test_a('x=99',a.id)
from a