如下的视图在oracle9i中怎么转换
create or replace view v_v8_tbl_agent_status as
(
select agent_code,update_time,user_code,user_name,user_area,current_queue_id,class_id,class_name,extension,current_status,
status_start_time,caller,computer_ip,call_center,signin_id,is_warn,warn_item,agent_sub_status,classwork_id,reserve_code1,
regexp_substr(queue_id,'[^;]+',1,line) as queue_id from tbl_agent_status,(select rownum as line from tbl_agent_status connect by rownum<=(select
max(length(queue_id)-length(replace(queue_id,';','')))+1 from tbl_agent_status)) where regexp_substr(queue_id,'[^;]+',1,line) is not null);谢谢!
create or replace view v_v8_tbl_agent_status as
(
select agent_code,update_time,user_code,user_name,user_area,current_queue_id,class_id,class_name,extension,current_status,
status_start_time,caller,computer_ip,call_center,signin_id,is_warn,warn_item,agent_sub_status,classwork_id,reserve_code1,
regexp_substr(queue_id,'[^;]+',1,line) as queue_id from tbl_agent_status,(select rownum as line from tbl_agent_status connect by rownum<=(select
max(length(queue_id)-length(replace(queue_id,';','')))+1 from tbl_agent_status)) where regexp_substr(queue_id,'[^;]+',1,line) is not null);谢谢!
是以;为分隔符,把queue_id分成N部分,然后从第1个字符,开始截取第line的部分的串,例:
select regexp_substr('aaa;bbbbb;cccc','[^;]+',1,2) from dual
结果是bbbbb。不过转的话,代码就多了。
曾桦 46-1;8-1;9-1
叶小叶 1-1;13-1;
原来是在Oracle10中的视图实现如下查询结果User_NAME QUEUE_ID
曾桦 46-1
曾桦 8-1
曾桦 9-1
叶小叶 1-1
叶小叶 13-1;但oracle9i 中 没有regexp_substr 应该如何转换
create or replace view v_v8_tbl_agent_status as
select agent_code,
update_time,
user_code,
user_name,
user_area,
current_queue_id,
class_id,
class_name,
extension,
current_status,
status_start_time,
caller,
computer_ip,
call_center,
signin_id,
is_warn,
warn_item,
agent_sub_status,
classwork_id,
reserve_code1,
substr(queue_id,line,instr(queue_id||';',';',line)-line) as queue_id
from tbl_agent_status,
(SELECT LEVEL line FROM DUAL CONNECT BY LEVEL<=100)
where substr(';'||queue_id,line,1)=';';
--测试
[SYS@myoracle] SQL>with tbl_agent_status as
2 (select '曾桦' a, '46-1;8-1;9-1' queue_id from dual
3 union all
4 select '叶小叶' a, '1-1;13-1' queue_id from dual)
5 select a, substr(queue_id, line, instr(queue_id || ';', ';', line) - line)
6 from tbl_agent_status,
7 (SELECT LEVEL line FROM DUAL CONNECT BY LEVEL <= 100)
8 where substr(';' || queue_id, line, 1) = ';';A SUBSTR(QUEUE_ID,LINE,INS
------ ------------------------
曾桦 46-1
叶小叶 1-1
叶小叶 13-1
曾桦 8-1
曾桦 9-1[/code]
create or replace view v_v8_tbl_agent_status as
select agent_code,
update_time,
user_code,
user_name,
user_area,
current_queue_id,
class_id,
class_name,
extension,
current_status,
status_start_time,
caller,
computer_ip,
call_center,
signin_id,
is_warn,
warn_item,
agent_sub_status,
classwork_id,
reserve_code1,
substr(queue_id,line,instr(queue_id||';',';',line)-line) as queue_id
from tbl_agent_status,
(SELECT LEVEL line FROM DUAL CONNECT BY LEVEL<=100)
where substr(';'||queue_id,line,1)=';';--测试
[SYS@myoracle] SQL>with tbl_agent_status as
2 (select '曾桦' a, '46-1;8-1;9-1' queue_id from dual
3 union all
4 select '叶小叶' a, '1-1;13-1;' queue_id from dual)
5 select a, substr(queue_id, line, instr(rtrim(queue_id,';') || ';', ';', line) - line)
6 from tbl_agent_status,
7 (SELECT LEVEL line FROM DUAL CONNECT BY LEVEL <= 100)
8 where substr(';' || rtrim(queue_id,';'), line, 1) = ';';A SUBSTR(QUEUE_ID,LINE,INS
------ ------------------------
曾桦 46-1
叶小叶 1-1
叶小叶 13-1
曾桦 8-1
曾桦 9-1