CODEWITHSUNDEEP
oracle-databasecaselistagg
Arty155
Arty155

Reputation: 113

Oracle listagg with CASE

I have 2 tables which are joined on order_item_xm.par_rowid = order_item.oi_rowid:

create table order_item_xm (oix_rowid varchar2(10),
                            par_rowid varchar2(10),
                            type      varchar2(30),
                            name      varchar2(30) );
                  
create table order_item (oi_rowid       varchar2(10) );
                         
insert into order_item_xm values ('3-AUXHJ', '3-BHUYI', 'ATS', '2-ATS0J');
insert into order_item_xm values ('3-AUXHK', '3-BHUYI', 'CART', 'a4beecj02kl');
insert into order_item_xm values ('3-AUXBW', '3-AKTKZ', 'ATS', 'SA-92');
insert into order_item_xm values ('3-AUXCY', '3-AKTKZ', 'CART', 'b3kl-hg7tcrd');
insert into order_item_xm values ('3-AUXMN', '3-AKTKZ', 'CART', 'n9-vgnj78lsd');
insert into order_item_xm values ('3-CKITY', '3-KB8UI', 'ATS', '2-ATS8U');
insert into order_item_xm values ('3-CMITY', '3-KB8UI', 'HES', 'HES09');
insert into order_item_xm values ('3-CZUIO', '3-KB8UI', 'HES', 'HES04');
insert into order_item_xm values ('3-CZAST', '3-KB8UI', 'ATS', '2-ATS6R');

insert into order_item(oi_rowid) values ('3-BHUYI');
insert into order_item(oi_rowid) values ('3-AKTKZ');
insert into order_item(oi_rowid) values ('3-KB8UI');

I want to join the 2 tables and get a pseudo column named "cart id" when column named type in table order_item_xm has value of CART. If the value is not CART then string "No Cart ID found" should be displayed.

Below is my query :

select a.oi_rowid, b.type, listagg(case b.type
                                      WHEN 'CART' THEN b.name
                                      ELSE 'No cart id found'
                                    end, ',') within group(order by b.name) as cart_id
from order_item a INNER JOIN order_item_xm b ON (a.oi_rowid = b.PAR_ROWID) group by a.oi_rowid, b.type;

And the output is :

enter image description here

Here is my problem 1: In the oi_rowid value 3-KB8UI there is no value CART in column type but because of listagg the string "No Cart ID found" appears twice. Is there anyway to not do that - basically do not concatenate values of column TYPE when the value CART is not present.

2nd : For oi_rowid value of 3-BHUYI value a4beecj02kl for cart_id should appear for ATS too. Same goes for oi_rowid value of 3-AKTKZ - value "b3kl-hg7tcrd,n9-vgnj78lsd" for cart_id should appear for ATS too.

Expected output is :

enter image description here

Can someone please help with this query?

EDIT : Sorry I updated the expected output. The previous one was a mistake. This is the correct one.

Upvotes: 1

Views: 158

Answers (1)

sticky bit
sticky bit

Reputation: 37472

Do the CASE after the aggregation, you still got the type there.

SELECT a.oi_rowid,
       b.type,
       CASE b.type
         WHEN 'CART' THEN
           listagg(b.name, ',') WITHIN GROUP (ORDER BY b.name)
         ELSE
           'No cart id found'
       END cart_id
       FROM order_item a
            INNER JOIN order_item_xm b
                       ON a.oi_rowid = b.par_rowid
       GROUP BY a.oi_rowid,
                b.type;

db<>fiddle

Edit:

To simulate a windowed and filtered version of listagg() -- as far as I can tell, there is no native one that allows for filters -- you can use correlated subqueries.

WITH cte
AS
(
SELECT a.oi_rowid,
       b.type,
       b.name
       FROM order_item a
            INNER JOIN order_item_xm b
                       ON a.oi_rowid = b.par_rowid
)
SELECT co.oi_rowid,
       co.type,
       CASE
         WHEN EXISTS (SELECT *
                             FROM cte ci
                             WHERE ci.oi_rowid = co.oi_rowid
                                   AND ci.type = 'CART') THEN
           (SELECT listagg(ci.name, ',') WITHIN GROUP (ORDER BY ci.name)
                   FROM cte ci
                   WHERE ci.oi_rowid = co.oi_rowid
                         AND ci.type = 'CART')
         ELSE
           'No cart id found'
       END cart_id
       FROM cte co
       ORDER BY co.oi_rowid,
                co.type;

db<>fiddle

Upvotes: 1

Related Questions