wTrying to write a CASE statement in Oracle. Not sure how to add this restriction-CodePudding

So this is the table -

create table inventoryItem (
       itemNum    varchar(10) not null
              references itemType(itemNum) on delete cascade,
       code   varchar(20) not null,
       qtyInstock float       not null,
       itemColor  varchar(15),
       itemSize   float,
       primary key (itemNum, code)
);

These are the values

insert into inventoryItem values ('A0', 'pbk', 30, 'black', 3.0);
insert into inventoryItem values ('B1', 'hbk', 50, 'white', 4.0);
insert into inventoryItem values ('A0', 'hbk', 25, 'green', 4.0);
insert into inventoryItem values ('C2', 'hbk', 20, 'black', 3.0);
insert into inventoryItem values ('C1', 'ebk', 70, 'black', 4.0);
insert into inventoryItem values ('A3', 'pbk', 30, 'black', 2.0);
insert into inventoryItem values ('A2', 'pbk', 50, 'green', 3.0);
insert into inventoryItem values ('A0', 'ebk', 15, 'green', 4.0);
insert into inventoryItem values ('C2', 'pbk', 30, 'black', 3.0);

I am attempting to write a subquery that firstly selects the itemNum, itemColor, itemSize and how much qty of stock each occurrence has. After this, the CASE statement needs to return an outcome. Lets say 'Happy' if the item number does not occur more than once with the same colour and size. Otherwise 'Sad'. The problem I have is how to state this in Oracle. This is the first part completed -

SELECT LISTAGG(itemnum, ', ') WITHIN GROUP (ORDER BY itemnum) itemNum,
itemsize, itemcolor, SUM(qtyinstock) qty_of_combination
FROM inventoryitem
GROUP BY itemsize, itemcolor;

This was my awful attempt at the CASE statement -

SELECT LISTAGG(itemNum, ', ') WITHIN GROUP (ORDER BY itemNum) itemNum,
itemSize, itemColor, SUM(qtyinstock) qty_of_combination, (
    SELECT itemNum, itemColor, itemSize, 
        CASE WHEN COUNT(itemNum) <= 1 THEN 'BR-2 Satisifed'
        ELSE 'BR-2 Violated'
        END AS itemNumRules
    FROM inventoryItem
    )
FROM inventoryItem
GROUP BY itemSize, itemColor;

This query completed the first part but I am not sure what exactly I need to put in this CASE statement. Any help is extremely appreciated!

CodePudding user response：

Check this, please:

SELECT
    COUNT(T1.itemNum),
    LISTAGG(T1.itemNum, ', ') WITHIN GROUP (
    ORDER BY T1.itemNum) itemNum,
    T1.itemSize,
    T1.itemColor,
    SUM(T1.qtyinstock) qty_of_combination,
    T2.itemNumRules
FROM
    inventoryItem T1
INNER JOIN (
    SELECT
        itemNum,
        CASE
            WHEN COUNT(itemNum) < 2 THEN 'BR-2 Satisifed'
            ELSE 'BR-2 Violated'
        END AS itemNumRules
    FROM
        inventoryItem
    GROUP BY
        itemNum) T2 ON
    T1.itemNum = T2.itemNum
GROUP BY
    T1.itemSize,
    T1.itemColor,
    T2.itemNumRules;

Hope it helps

CodePudding user response：

Since you have not posted your expected result, A simple COUNT analytical function is what I understand you need. -

SELECT LISTAGG(itemnum, ', ') WITHIN GROUP (ORDER BY itemnum) itemNum,
       itemsize, 
       itemcolor, 
       SUM(qtyinstock) qty_of_combination, 
       CASE WHEN cnt <= 1 THEN 'BR-2 Satisifed' ELSE 'BR-2 Violated' END itemNumRules
  FROM (SELECT itemnum, itemsize, itemcolor, qtyinstock, 
               COUNT(*) OVER(partition by itemsize, itemcolor) cnt
          FROM inventoryitem)
 GROUP BY itemsize, 
          itemcolor, 
          cnt;

Demo.