Oracle function to select multiple rows in a single row not working

Question

I am loading a file into a staging record using Application Engine. when ever there is blank in BU,Deptid etc... I have to capture what all columns are blank and update error text field with those values.

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' AND ACTION = 'E' AND PROCESS_INSTANCE = '9852429'

The above script results as below.

ERROR

BUSINESS_UNIT IS NULL DEPTID IS NULL

I want the result as below.

ERROR

BUSINESS_UNIT IS NULL,DEPTID IS NULL

I am using ListAgg function but facing errors as below.Any help would be great.

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

Facing the error:

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

seems fine from this end, I run essentially the same query on my end (against a junk table, but same sql you got there) .. works fine. Only thing I can think of is any special/hidden characters in there anywhere ? — Ditto
What Oracle version are you using? LISTAGG was introduced in 11gR2, see oracle-base.com/articles/12c/… — Frank Schmitt
Also, your SQL statement looks buggy - filtering by user_id and also using the user_id in the ORDER BY clause will give you a random ordering, and putting business_unit both inside LISTAGG and inside your GROUP BYwill only concatenate the same business_unit multiple times. What are you trying to achieve? Editing your question and adding sample input and expected output would help tremendously. — Frank Schmitt
That error suggests a syntax error, but the query works for me in sqlfiddle. Your problem must lie elsewhere. — eaolson
Thanks a lot for the suggestions. I would better put here what I am trying to achive. — yashwanth asady

Ponder Stibbons Ponder Stibbons · Accepted Answer · 2019-04-02T14:19:19

You overcomplicated things. You don't need two unions and listagg at all. Simple use concatenation of case... when, write your select like here:

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'

^demo

If your table has primary key then use it for update. Otherwise you can use merge with rowid. Or do it the way you did it if it worked for you, especially if there is only one row meeting criteria.

Oracle function to select multiple rows in a single row not working

ERROR

ERROR

1 Answers