I have a statement below which is not working the way I want it to, here is my code:
select
case when o.personal_label is not null
then
listagg(case when s.subcodevalue like '%MON%'
then s.subcodevalue||' '||o.personal_label
else s.subcodevalue
end,': ')
within group (order by s.subcodevalue) as subcodevalue
else
listagg(s.subcodevalue,': ')
within group (order by s.subcodevalue) as subcodevalue
end
from mtm_styles_new s, order_mtm o
where INSTR(o.extras,s.code) > 0
and o.bodyfitting=s.bodyfitting
and o.division=s.division
and s.subcodevalue is not null
and o.ORDER_MTMID = 'somevalue'
It should bring back the following
*MON TESTING: PSUR
or the following if the first case is not met:
*MON: PSUR
I am getting an error saying "missing keyword", but can't make out where, can someone please assist me here?
The following works fine as it is not a nested case statement but I need to nest the case statement:
select listagg(case when s.subcodevalue like '%MON%'
then s.subcodevalue||' '||o.personal_label
else s.subcodevalue
end,': ')
within group (order by s.subcodevalue) as subcodevalue
from mtm_styles_new s, order_mtm o
where INSTR(o.extras,s.code) > 0
and o.bodyfitting=s.bodyfitting
and o.division=s.division
and s.subcodevalue is not null
and o.ORDER_MTMID = 'somevalue';