表A
only_unit_code,run_condition,run_result,last_run_date
其中run_condition为已冒号作为分隔符,连接的only_unit_code字符串
数据:
'P_TAPRG_0014','P_TAPRG_0031:P_TAPRG_0040',0,'20100416'
'P_TAPRG_0031','P_TAPRG_0001:P_TAPRG_0002',0,'20100416'
'P_TAPRG_0040','P_TAPRG_0003:P_TAPRG_0004',0,'20100416'
'P_TAPRG_0003','P_TAPRG_0005:',0,'20100416'
'P_TAPRG_0004','P_TAPRG_0006:P_TAPRG_0007:P_TAPRG_0008',0,'20100416'
我的意思是:only_unit_code是程序号,run_condition里面是此程序号运行条件,
即此程序号运行前提必须是run_condition里面的所有已冒号作为分隔符的程序已运行且run_result为0运行成功现在已知的查询条件为 ONLY_UNIT_CODE='P_TAPRG_0014',时间暂时先忽略
根据这个条件查出这个程序是否能运行,请各位想想办法看怎么能一条sql搞定。
难在run_condition里面的冒号个数是不确定的。
only_unit_code,run_condition,run_result,last_run_date
其中run_condition为已冒号作为分隔符,连接的only_unit_code字符串
数据:
'P_TAPRG_0014','P_TAPRG_0031:P_TAPRG_0040',0,'20100416'
'P_TAPRG_0031','P_TAPRG_0001:P_TAPRG_0002',0,'20100416'
'P_TAPRG_0040','P_TAPRG_0003:P_TAPRG_0004',0,'20100416'
'P_TAPRG_0003','P_TAPRG_0005:',0,'20100416'
'P_TAPRG_0004','P_TAPRG_0006:P_TAPRG_0007:P_TAPRG_0008',0,'20100416'
我的意思是:only_unit_code是程序号,run_condition里面是此程序号运行条件,
即此程序号运行前提必须是run_condition里面的所有已冒号作为分隔符的程序已运行且run_result为0运行成功现在已知的查询条件为 ONLY_UNIT_CODE='P_TAPRG_0014',时间暂时先忽略
根据这个条件查出这个程序是否能运行,请各位想想办法看怎么能一条sql搞定。
难在run_condition里面的冒号个数是不确定的。
only_unit_code, -- 程序号
run_condition, -- 运行前置条件
run_result, -- 运行结果
last_run_date -- 上次运行时间
(select 'P_TAPRG_0014' only_unit_code,'P_TAPRG_0031:P_TAPRG_0040' run_condition,0 run_result,'20100416' last_run_date from dual
union all
select 'P_TAPRG_0014' only_unit_code,'P_TAPRG_0031:P_TAPRG_0014' run_condition,0 run_result,'20100416' last_run_date from dual
)
select * from tt
where instr(run_condition,'P_TAPRG_0014')>0
CASE WHEN (SELECT count(distinct only_unit_code) FROM TABNAME
where ':'||t.run_condition||':' like '%:'||only_unit_code||':%'
and run_result=0)=length(t.run_condition)-length(replace(t.run_condition,':'))+1
then 'true' else 'false' end flag
from TABNAME t
where only_unit_code='P_TAPRG_0014';
select 'P_TAPRG_0014' only_unit_code,'P_TAPRG_0031:P_TAPRG_0040' run_condition,0 run_result,'20100416' last_run_date from dual union all
select 'P_TAPRG_0031' only_unit_code,'P_TAPRG_0001:P_TAPRG_0002' run_condition,0 run_result,'20100416' last_run_date from dual union all
select 'P_TAPRG_0040' only_unit_code,'P_TAPRG_0003:P_TAPRG_0004' run_condition,0 run_result,'20100416' last_run_date from dual union all
select 'P_TAPRG_0003' only_unit_code,'P_TAPRG_0005:' run_condition,0 run_result,'20100416' last_run_date from dual union all
select 'P_TAPRG_0004' only_unit_code,'P_TAPRG_0006:P_TAPRG_0007:P_TAPRG_0008' run_condition,0 run_result,'20100416' last_run_date from dual )
SELECT decode(COUNT(*), 0, 'OK', 'FAIL') RESULT
FROM (SELECT *
FROM p
WHERE (SELECT ':' || run_condition || ':' FROM p WHERE only_unit_code = 'P_TAPRG_0014') LIKE
'%:' || only_unit_code || ':%')
WHERE run_result <> 0;
run_condition='P_TAPRG_0031:P_TAPRG_0040,只能判断ONLY_UNIT_CODE='P_TAPRG_0031'
ONLY_UNIT_CODE='P_TAPRG_0040'的状态情况来判断当ONLY_UNIT_CODE='P_TAPRG_0014'时
run_result的是否可以运行!
如果这个假设成立的话,那可以看下边的内容,并请多给些分数!!!
with table1 as
(select 'P_TAPRG_0014' only_unit_code,'P_TAPRG_0031:P_TAPRG_0040' run_condition,0 run_result,'20100416' last_run_date from dual
union all
select 'P_TAPRG_0031' only_unit_code,'P_TAPRG_0001:P_TAPRG_0002' run_condition,0 run_result,'20100416' last_run_date from dual
union all
select 'P_TAPRG_0040' only_unit_code,'P_TAPRG_0003:P_TAPRG_0004' run_condition,0 run_result,'20100416' last_run_date from dual
union all
select 'P_TAPRG_0003' only_unit_code,'P_TAPRG_0005:' run_condition,0 run_result,'20100416' last_run_date from dual
union all
select 'P_TAPRG_0004' only_unit_code,'P_TAPRG_0006:P_TAPRG_0007:P_TAPRG_0008' run_condition,0 run_result,'20100416' last_run_date from dual
)
select (case when sum(run_result) > 0 then '不能执行' else '能够执行' end) 结果 from table1 where only_unit_code in
(
with table2 as
(
select 'P_TAPRG_0014' only_unit_code,'P_TAPRG_0031:P_TAPRG_0040' run_condition,0 run_result,'20100416' last_run_date from dual
union all
select 'P_TAPRG_0031' only_unit_code,'P_TAPRG_0001:P_TAPRG_0002' run_condition,0 run_result,'20100416' last_run_date from dual
union all
select 'P_TAPRG_0040' only_unit_code,'P_TAPRG_0003:P_TAPRG_0004' run_condition,0 run_result,'20100416' last_run_date from dual
union all
select 'P_TAPRG_0003' only_unit_code,'P_TAPRG_0005:' run_condition,0 run_result,'20100416' last_run_date from dual
union all
select 'P_TAPRG_0004' only_unit_code,'P_TAPRG_0006:P_TAPRG_0007:P_TAPRG_0008' run_condition,0 run_result,'20100416' last_run_date from dual
)
select (case
when instr(run_condition, ':', -1, 1) - length(trim(run_condition)) = 0 then
'''' || replace(run_condition, ':', '''') || ''''
else
'''' || replace(run_condition, ':', ''',''') || ''''
end) run_condition
from table2
where only_unit_code = 'P_TAPRG_0014')
思路:至于为什么这么写很简单因为这个语句
select '可以' from dual where 'a' in ('a','b','c','d');
with p as (
select 'P_TAPRG_0014' only_unit_code,'P_TAPRG_0031:P_TAPRG_0040' run_condition,0 run_result,'20100416' last_run_date from dual union all
select 'P_TAPRG_0031' only_unit_code,'P_TAPRG_0001:P_TAPRG_0002' run_condition,0 run_result,'20100416' last_run_date from dual union all
select 'P_TAPRG_0040' only_unit_code,'P_TAPRG_0003:P_TAPRG_0004' run_condition,0 run_result,'20100416' last_run_date from dual union all
select 'P_TAPRG_0003' only_unit_code,'P_TAPRG_0005:' run_condition,0 run_result,'20100416' last_run_date from dual union all
select 'P_TAPRG_0004' only_unit_code,'P_TAPRG_0006:P_TAPRG_0007:P_TAPRG_0008' run_condition,0 run_result,'20100416' last_run_date from dual )
,p1 as (SELECT replace(wm_concat(p.run_condition),',',':') a
FROM p
Where run_result = 0
Connect By instr((SELECT ':' || run_condition || ':' FROM p WHERE only_unit_code = 'P_TAPRG_0014'),'%:' || only_unit_code || ':%') <> 0
),p2 as ( SELECT replace(replace(wm_concat(p.only_unit_code),',',':'),'P_TAPRG_0014:') a
FROM p
Where run_result = 0
Connect By instr((SELECT ':' || run_condition || ':' FROM p WHERE only_unit_code = 'P_TAPRG_0014'),'%:' || only_unit_code || ':%') <> 0
),p3 As(
SELECT substr(t1.unit_code,
instr(t1.unit_code, ':', 1, LEVEL) + 1,
decode(instr(t1.unit_code, ':', 1, LEVEL + 1),
0,
length(unit_code) + 1,
instr(t1.unit_code, ':', 1, LEVEL + 1)) - instr(t1.unit_code, ':', 1, LEVEL) - 1) s
FROM dual, (Select ':'|| a unit_code From p1)t1
CONNECT BY LEVEL <= (SELECT length(unit_code) - length(REPLACE(unit_code, ':')) FROM p1)
) ,p4 As (
SELECT substr(t1.unit_code,
instr(t1.unit_code, ':', 1, LEVEL) + 1,
decode(instr(t1.unit_code, ':', 1, LEVEL + 1),
0,
length(unit_code) + 1,
instr(t1.unit_code, ':', 1, LEVEL + 1)) - instr(t1.unit_code, ':', 1, LEVEL) - 1) s
FROM dual, (Select ':'|| a unit_code From p2) t1
CONNECT BY LEVEL <= (SELECT length(unit_code) - length(REPLACE(unit_code, ':')) FROM p2)
)
Select decode(count(Distinct p3.s),0,'可以','不可以') flag
From p3
Where
Not Exists
(Select p4.s From p4 Where p4.s = p3.s)
这个??!感觉这个功能用过程更好啊