I have the following table EPS_PROPOSAL_EXT_T that I've condensed:
+--------------------+------------+------------+
| PROPOSAL_NUMBER |ONR_OPTION_1|ONR_Option_2| ...
+--------------------+------------+------------+
| 1234 | N | N | ...
| 1235 | Y | Y | ...
| 1236 | N | Y | ...
+--------------------+------------+------------+
The problem here is that I need to do a LISTAGG across all those different ONR_Options, but I can't have them show up as 'Y'. I need to replace the ones that are 'Y' with something human understandable. See SELECT statement below where I've attempted to do this:
SELECT eps.PROPOSAL_NUMBER, eps.TITLE, per.FULL_NAME, ext.NRP_IREF_CD, ext.RESEARCH_TYPE_CD,
nsf.NSF_CODE, ext.NPS_THRUST_DESCRIPTION,
CASE
WHEN ext.ONR_Option_1 = 'Y' THEN 'Option 1'
WHEN ext.ONR_Option_2 = 'Y' THEN 'Option 2'
WHEN ext.ONR_Option_3 = 'Y' THEN 'Option 3'
WHEN ext.ONR_Option_4 = 'Y' THEN 'Option 4'
WHEN ext.ONR_Option_5 = 'Y' THEN 'Option 5'
WHEN ext.ONR_Option_6 = 'Y' THEN 'Option 6'
WHEN ext.ONR_Option_7 = 'Y' THEN 'Option 7'
WHEN ext.ONR_Option_8 = 'Y' THEN 'Option 8'
WHEN ext.ONR_Option_9 = 'Y' THEN 'Option 9'
WHEN ext.ONR_NOT_APPLICABLE = 'Y' THEN 'Not Applicable'
ELSE ''
END ONR
FROM EPS_PROPOSAL eps
LEFT JOIN EPS_PROPOSAL_EXT_T ext
ON eps.PROPOSAL_NUMBER = ext.PROPOSAL_NUMBER
LEFT JOIN EPS_PROP_PERSON per
ON eps.PROPOSAL_NUMBER = per.PROPOSAL_NUMBER AND
(per.PROP_PERSON_ROLE_ID = 'PI' OR per.PROP_PERSON_ROLE_ID = 'PD')
LEFT JOIN EPS_PROP_ABSTRACT abs
ON eps.PROPOSAL_NUMBER = abs.PROPOSAL_NUMBER
LEFT JOIN NSF_CODES nsf
ON eps.NSF_CODE = nsf.NSF_SEQUENCE_NUMBER
WHERE eps.OWNED_BY_UNIT = '401' AND eps.requested_start_date_initial >= DATE '2019-10-01';
This works but you can see the problem with it, right? The way I've setup my CASE statement, it will not take into account when for a particular PROPOSAL_NUMBER there is more than one ONR_Option that is TRUE.
In the example I gave above, if I search for a proposal with number = 1235; the ONR result should be - 'Option 1, Option 2'.
How do I solve this dilemma with LISTAGG and COALESCE? Or is that the way to go about this?
LISTAGG doesn't help here. Why? Because your data model is wrong. In my opinion, should be something like this:
SQL> with test (prop, onr, status) as
2 (select 1234, '1', 'N' from dual union all
3 select 1234, '2', 'N' from dual union all
4 select 1234, '3', 'Y' from dual union all
5 select 1235, '1', 'Y' from dual union all
6 select 1235, '2', 'Y' from dual union all
7 select 1235, '3', 'N' from dual
8 )
9 select prop,
10 listagg(case when status = 'Y' then 'Option ' || onr end, ',')
11 within group (order by onr) as result
12 from test
13 group by prop;
PROP RESULT
---------- ------------------------------
1234 Option 3
1235 Option 1,Option 2
SQL>
The way it currently is, see if this - concatenation of as many CASEs as there are ONR_OPTION columns in the table, which scales as a goat you'd like to teach to fly, along with pain of removing multiple commas - helps:
SQL> with test (prop, onr_1, onr_2, onr_3) as
2 (select 1234, 'N', 'N', 'Y' from dual union all
3 select 1235, 'Y', 'Y', 'N' from dual
4 )
5 select prop,
6 trim(both ',' from
7 case when onr_1 = 'Y' then 'Option 1' else null end ||','||
8 case when onr_2 = 'Y' then 'Option 2' else null end ||','||
9 case when onr_3 = 'Y' then 'Option 3' else null end
10 ) as result
11 from test;
PROP RESULT
---------- ------------------------------
1234 Option 3
1235 Option 1,Option 2
SQL>