时间戳

首页 > 解决方案 > Oracle函数在单行中选择多行不起作用

oracle - Oracle函数在单行中选择多行不起作用

问题描述

我正在使用应用程序引擎将文件加载到暂存记录中。当 BU、Deptid 等中有空白时...我必须捕获所有列都是空白的并使用这些值更新错误文本字段。

UPDATE SYSADM.PS_VI_EMP_TS SET ERR_TEXT = SELECT ERROR FROM (SELECT * FROM (SELECT CASE WHEN BUSINESS_UNIT = ' ' THEN 'BUSINESS_UNIT IS NULL' ELSE ' ' END AS ERROR FROM SYSADM.PS_VI_EMP_TS WHERE USERID='JCOOPER' AND ACTION = 'E' AND PROCESS_INSTANCE = '7852429' UNION SELECT CASE WHEN DEPTID = ' ' THEN 'DEPTID IS NULL' ELSE ' ' END AS ERROR from SYSADM.PS_VI_EMP_TS WHERE USERID='JCOOPER' AND ACTION = 'E' AND PROCESS_INSTANCE = '9852429' UNION SELECT CASE WHEN PROJECT_ID =' ' THEN 'PROJECT_ID IS NULL' ELSE ' ' END AS ERROR FROM SYSADM.PS_VI_EMP_TS WHERE USERID='JCOOPER' AND ACTION = 'E' AND PROCESS_INSTANCE = '9852429' )) WHERE ERROR <> ' ' WHERE USERID ='JCOOPER' 和行动 = 'E' AND PROCESS_INSTANCE = '9852429'

上面的脚本结果如下。

错误

BUSINESS_UNIT 为 NULL DEPTID 为 NULL

我想要的结果如下。

错误

BUSINESS_UNIT 为 NULL，DEPTID 为 NULL

我正在使用 ListAgg 函数，但遇到如下错误。任何帮助都会很棒。

SELECT LISTAGG(BUSINESS_UNIT, ';') WITHIN GROUP(ORDER BY USERID) 
FROM SYSADM.PS_VI_EMP_TS WHERE USERID='JCOOPER'
GROUP BY BUSINESS_UNIT

面对错误：

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 47 Column: 43

标签： oraclelistagg

解决方案

你把事情复杂化了。你根本不需要两个工会listagg。的简单使用连接case... when，在这里写你的选择：

select case business_unit when ' ' then 'BUSINESS_UNIT IS NULL; ' end ||
       case deptid        when ' ' then 'DEPTID IS NULL; '        end ||
       case project_id    when ' ' then 'PROJECT_ID IS NULL; '    end as error
  from ps_vi_emp_ts 
  where userid = 'JCOOPER' and action = 'E' and process_instance = '9852429'

演示

如果您的表有主键，则使用它进行更新。否则，您可以merge与 rowid 一起使用。或者如果它对你有用，就按照你的方式去做，特别是如果只有一行符合标准。

推荐阅读