CTE

You must execute the code again to save those values in the database.

-- 建立訂單主表
CREATE TABLE ORDERS (
  ORDER_ID     VARCHAR2(10),  -- 訂單編號
  CUSTOMER_ID  VARCHAR2(10),  -- 顧客編號
  ORDER_DATE   DATE           -- 訂單日期
);

-- 建立訂單明細表
CREATE TABLE ORDER_ITEMS (
  ORDER_ID      VARCHAR2(10),  -- 對應的訂單編號
  PRODUCT_ID    VARCHAR2(10),  -- 商品ID
  PRODUCT_NAME  VARCHAR2(100)  -- 商品名稱
);

-- 插入訂單資料
INSERT INTO ORDERS VALUES ('1001', 'C001', TO_DATE('2025-01-01', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1002', 'C002', TO_DATE('2025-01-11', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1003', 'C003', TO_DATE('2025-02-02', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1004', 'C004', TO_DATE('2025-03-03', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1005', 'C005', TO_DATE('2025-03-23', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1006', 'C002', TO_DATE('2025-02-22', 'YYYY-MM-DD'));

-- 插入訂單品項資料
INSERT INTO ORDER_ITEMS VALUES ('1001', 'P001', 'Camera');
INSERT INTO ORDER_ITEMS VALUES ('1001', 'P002', 'Tripod');
INSERT INTO ORDER_ITEMS VALUES ('1002', 'P003', 'Laptop');
INSERT INTO ORDER_ITEMS VALUES ('1003', 'P001', 'Camera');
INSERT INTO ORDER_ITEMS VALUES ('1003', 'P003', 'Laptop');
INSERT INTO ORDER_ITEMS VALUES ('1004', 'P004', 'Coffee');
INSERT INTO ORDER_ITEMS VALUES ('1005', 'P005', 'Bicycle');
INSERT INTO ORDER_ITEMS VALUES ('1006', 'P006', 'DIY Kit');

-- 提交資料異動
COMMIT;

WITH TAG_ITEMS AS (
  SELECT
    O.CUSTOMER_ID,
    O.ORDER_DATE,
    CASE 
      WHEN UPPER(OI.PRODUCT_NAME) LIKE '%CAMERA%' THEN 'Photography Lover'
      WHEN UPPER(OI.PRODUCT_NAME) LIKE '%TRIPOD%' THEN 'Photography Lover'
      WHEN UPPER(OI.PRODUCT_NAME) LIKE '%LAPTOP%' THEN 'Tech Enthusiast'
      WHEN UPPER(OI.PRODUCT_NAME) LIKE '%COFFEE%' THEN 'Coffee Enthusiast'
      WHEN UPPER(OI.PRODUCT_NAME) LIKE '%BICYCLE%' THEN 'Fitness Lifestyle'
      WHEN UPPER(OI.PRODUCT_NAME) LIKE '%DIY%' THEN 'Craft Enthusiast'
    END AS TAG
  FROM ORDERS O
  JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
  WHERE OI.PRODUCT_NAME IS NOT NULL
)
SELECT * FROM TAG_ITEMS;

CUSTOMER_I	ORDER_DAT	TAG
C001	01-JAN-25	Photography Lover
C001	01-JAN-25	Photography Lover
C002	11-JAN-25	Tech Enthusiast
C003	02-FEB-25	Photography Lover
C003	02-FEB-25	Tech Enthusiast
C004	03-MAR-25	Coffee Enthusiast
C005	23-MAR-25	Fitness Lifestyle
C002	22-FEB-25	Craft Enthusiast

CUSTOMER_I	ORDER_DAT	TAG
C001	01-JAN-25	Photography Lover
C001	01-JAN-25	Photography Lover
C002	11-JAN-25	Tech Enthusiast
C003	02-FEB-25	Photography Lover
C003	02-FEB-25	Tech Enthusiast
C004	03-MAR-25	Coffee Enthusiast
C005	23-MAR-25	Fitness Lifestyle
C002	22-FEB-25	Craft Enthusiast