Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE product_information (
  Product_ID      NUMBER(6,0),
  Warranty_Period INTERVAL YEAR(2) TO MONTH
);
INSERT INTO product_information (product_id, warranty_period)
SELECT 102, INTERVAL '1-6' YEAR TO MONTH FROM DUAL UNION ALL
SELECT 103, INTERVAL '4' MONTH FROM DUAL UNION ALL
SELECT 104, INTERVAL '0' MONTH FROM DUAL UNION ALL
SELECT 105, INTERVAL '1' MONTH FROM DUAL UNION ALL
SELECT 106, INTERVAL '1' YEAR FROM DUAL;
5 rows affected

SELECT product_id,
       CASE EXTRACT(YEAR FROM warranty_period)
       WHEN 0 THEN NULL
       WHEN 1 THEN '1 year'
       ELSE EXTRACT(YEAR FROM warranty_period) || ' years'
       END
       || CASE
       WHEN EXTRACT(YEAR FROM warranty_period) > 0
       AND  EXTRACT(MONTH FROM warranty_period) > 0
       THEN ' and '
       WHEN EXTRACT(YEAR FROM warranty_period) = 0
       AND  EXTRACT(MONTH FROM warranty_period) = 0
       THEN 'No warranty'
       END
       || CASE EXTRACT(MONTH FROM warranty_period)
       WHEN 0 THEN NULL
       WHEN 1 THEN '1 month'
       ELSE EXTRACT(MONTH FROM warranty_period) || ' months'
       END AS warranty
FROM   product_information;


PRODUCT_ID
WARRANTY


102
1 year and 6 months

103
4 months

104
No warranty

105
1 month

106
1 year

PRODUCT_ID	WARRANTY
102	1 year and 6 months
103	4 months
104	No warranty
105	1 month
106	1 year