CREATE TABLE source_det (
e_id NUMBER(10),
sys_name VARCHAR2(20),
ref_id NUMBER(10),
sys_other VARCHAR2(30)
);
INSERT INTO source_det VALUES(11,'SOURCE',992,null);
INSERT INTO source_det VALUES(11,'SOURCE',637,null);
INSERT INTO source_det VALUES(11,'SOURCE',null,'Manual');
INSERT INTO source_det VALUES(11,'TARGET',637,null);
commit;
My Attempt:
SELECT e_id,
LISTAGG(source, ';') source,
LISTAGG(target, ';') target,
source_other
FROM (SELECT e_id,
CASE
WHEN sys_name = 'SOURCE' THEN
ref_id
END source,
CASE
WHEN sys_name = 'TARGET' THEN
ref_id
END target,
CASE
WHEN sys_name = 'SOURCE' AND ref_id IS NULL THEN
sys_other
END source_other
FROM source_det
GROUP BY e_id);
From the above set of data I need only a single row to be returned from the SELECT
query but I am getting null
values instead along with the required values.
If sys_name
is SOURCE then the result should be ref_id
. If there is more than one record then it should be spilitted by delimiter ;
If sys_name
is TARGET then result should be ref_id
If sys_name
is SOURCE AND ref_id
is null then it should give sys_other
as a result. But from my query I am not getting a single row instead getting 4 rows. But ideally, I need only one row with e_id
11
And most important is in place of null values it could be possible that there would be ()
present in place of null. So, need to exclude this as well. After getting the desired result then we can use DECODE(ref_id,'()',null) like this.
Expected result:
e_id | source | target | source_other |
---|---|---|---|
11 | 992;637 | 637 | Manual |
Tool used: SQL Developer (18c)
CodePudding user response:
You don't need a subquery, just use like the following one below
SELECT e_id,
LISTAGG(CASE
WHEN sys_name = 'SOURCE' THEN
ref_id
END,
';') WITHIN GROUP(ORDER BY ref_id DESC) AS source,
LISTAGG(CASE
WHEN sys_name = 'TARGET' THEN
ref_id
END,
';') WITHIN GROUP(ORDER BY ref_id DESC) AS target,
LISTAGG(CASE
WHEN sys_name = 'SOURCE' AND ref_id IS NULL THEN
DECODE(sys_other,'()','',sys_other)
END,
';') WITHIN GROUP(ORDER BY 0) AS source_other
FROM source_det
GROUP BY e_id
where missing WITHIN GROUP (ORDER BY ..)
should follow LISTAGG(...)
expression