如果我没理解错的话，你的问题是--如果你取消注解那个代码的replace部分--你必须把它包含到group by子句中。
字面意思是：

SELECT    REPLACE (SUBSTR (p.prod_desc, 20, 200), 'size XXXL', 'size 14.00')
         || ':PRODUCT_ID='
         || p.prod_id AS "PRODUCT_DESCRIPTION",
         CASE
            WHEN t.calendar_week_number = 13 THEN 'First week'
            WHEN t.calendar_week_number = 14 THEN 'Second week'
            WHEN t.calendar_week_number = 15 THEN 'Third week'
            WHEN t.calendar_week_number = 16 THEN 'Fourth week'
            WHEN t.calendar_week_number = 17 THEN 'Fifth week'
         END AS "WEEK_IN_MONTH",
         SUM (s.amount_sold) AS total_amount_sold
    FROM times t
         JOIN sales s ON t.time_id = s.time_id
         JOIN products p ON p.prod_id = s.prod_id
   WHERE     t.calendar_month_desc = '2000-04'
         AND p.prod_id IN (300, 10, 540)
GROUP BY t.calendar_week_number,
            REPLACE (SUBSTR (p.prod_desc, 20, 200),
                     'size XXXL',
                     'size 14.00')
         || ':PRODUCT_ID='
         || p.prod_id
  HAVING SUM (s.amount_sold) > 0;

另一个选择是聚合代码的replace部分，这样就不需要将其放在group by中：

SELECT MAX (
               REPLACE (SUBSTR (p.prod_desc, 20, 200),
                        'size XXXL',
                        'size 14.00')
            || ':PRODUCT_ID='
            || p.prod_id) AS "PRODUCT_DESCRIPTION",
         CASE
            WHEN t.calendar_week_number = 13 THEN 'First week'
            WHEN t.calendar_week_number = 14 THEN 'Second week'
            WHEN t.calendar_week_number = 15 THEN 'Third week'
            WHEN t.calendar_week_number = 16 THEN 'Fourth week'
            WHEN t.calendar_week_number = 17 THEN 'Fifth week'
         END AS "WEEK_IN_MONTH",
         SUM (s.amount_sold) AS total_amount_sold
    FROM times t
         JOIN sales s ON t.time_id = s.time_id
         JOIN products p ON p.prod_id = s.prod_id
   WHERE     t.calendar_month_desc = '2000-04'
         AND p.prod_id IN (300, 10, 540)
GROUP BY t.calendar_week_number
  HAVING SUM (s.amount_sold) > 0;