DB2 – Determine if Combination of Values Exist and Write to New Column

db2group bypartitioning

I'm struggling with a problem.

An order can have multiple lines and accordingly a type (F=Footwear/N=Non-FTW,Mixed=Both)
I need to determine if the order consists of which type and create a new column with this information.

ORDR                         | TYPE
-----------------------------------------
#31900440220151005215630000  | F
#31900440220151005215630000  | N
#31900440220151005215630000  | N
#31900440220151005215680000  | N
#31900440220151005215680000  | N
#31900440220151005215680000  | N
#31900440220151005215680000  | N
#31900440220151005215680000  | N
#31900440220151005215710000  | F
#31900440220151005215720000  | F
#31900440220151005215740000  | F

What I want to have is ultimately:

ORDR                         | TYPE  | New column
------------------------------------------------
#31900440220151005215630000  | F    |   Mixed
#31900440220151005215630000  | N    |   Mixed
#31900440220151005215630000  | N    |   Mixed
#31900440220151005215680000  | N    |   Non-FTW
#31900440220151005215680000  | N    |   Non-FTW
#31900440220151005215680000  | N    |   Non-FTW
#31900440220151005215680000  | N    |   Non-FTW
#31900440220151005215680000  | N    |   Non-FTW
#31900440220151005215710000  | F    |   FTW
#31900440220151005215720000  | F    |   FTW
#31900440220151005215740000  | F    |   FTW

I thought of partition over but currently i'm stuck.

Best Answer

For an Update to the existing table, one solution would be:

UPDATE t
SET t.New_Column = CASE WHEN types.mintype = 'F' and types.maxtype = 'F' THEN 'FTW'
                        WHEN types.mintype = 'N' and types.maxtype = 'N' THEN 'Non-FTW'
                        WHEN types.mintype = 'F' AND types.maxtype = 'N' THEN 'Mixed' END
FROM tbl t
INNER JOIN 
(SELECT ORDR, 
       MIN(TYPE) mintype,
       MAX(TYPE) maxtype
FROM tbl
GROUP BY ORDR) types
    ON t.ORDR = types.ORDR

Though, I would also, as Chris Aldrich said in the comments, suggest using a view. That view could be:

CREATE VIEW v_tbl
AS 
SELECT t.ORDR, 
       t.TYPE,
       CASE WHEN types.mintype = 'F' and types.maxtype = 'F' THEN 'FTW'
            WHEN types.mintype = 'N' and types.maxtype = 'N' THEN 'Non-FTW'
            WHEN types.mintype = 'F' AND types.maxtype = 'N' THEN 'Mixed' END New_Column
FROM tbl t
INNER JOIN 
(SELECT ORDR, 
       MIN(TYPE) mintype,
       MAX(TYPE) maxtype
FROM tbl
GROUP BY ORDR) types
    ON t.ORDR = types.ORDR
Related Question