泛微OA的几个SQL小技巧_V1

1.弥补OA流程中的流程编号

1.1.打开OA流程后台的流程编号配置页面，选择预留编号，根据流程编号，输入需要预留的编号

1.2.执行sql语句，先修改workflow_requestbase 中的字段requestmark 然后修改对应流程中的lcbh字段

--需要注意sqlserver/mysql的updat语句的表不能设置标识
--修改流程编号的sql语句，先执行（1）再执行（2）
--（1）
update workflow_requestbase set requestmark = '2023111625497' where workflowid = '1021' and requestmark is null and requestid = '1836806';
--（2）
update formtable_main_83 set formtable_main_83.lcbh = (select requestmark from workflow_requestbase b where formtable_main_83.requestid=b.requestid) where formtable_main_83.lcbh = '' and formtable_main_83.requestid = '1836806'
--Oracle
--（1）
update workflow_requestbase set requestmark = 'XCTYCS0_'||substr(createdate,0,4)||substr(createdate,6,2)||lpad(rownum,4,0) where workflowid = '23186' and requestmark is null;
--（2）
update formtable_main_753 a set lcbh = (select requestmark from workflow_requestbase b where a.requestid=b.requestid) where a.lcbh is null;

2.流程中多选浏览框关联key关联value

--oracle
select (select listagg(subcompanyname, ',') within group(order by id)
              from hrmsubcompany
             where --fieldid = '520069'
               id in
                   (select regexp_substr(sydw, '[^,]+', 1, rownum)
                      from dual
                    connect by rownum <=
                               length(sydw) - length(replace(sydw, ',', '')) + 1))
from ecology.formtable_main_709

3.去除节点名称中特殊字符以及繁体字

--oracle
select ltrim(regexp_substr(nodename,'[^~`]+',1,1),'7') from workflow_nodebase where nodename like '%~%'