so I have table Product_Information
Product | Data type |
---|---|
Product_ID | NUMBER(6,0) |
Warranty_Period | INTERVAL YEAR(2) TO MONTH |
How should I do a diapason check between intervals? This is the code I've tried
case W.WARRANTY_PERIOD
when INTERVAL '0-0' YEAR TO MONTH THEN 'No warranty'
when INTERVAL '0-3' YEAR TO MONTH THEN '3 months'
end as Warranty
I've already joined the table, but I need result like this:
ProductID | Warranty Left |
---|---|
102 | 1 year and 6 months |
103 | 4 months |
Use EXTRACT
in a CASE
expression:
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;
Which, for the sample data:
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;
Outputs:
PRODUCT_ID WARRANTY 102 1 year and 6 months 103 4 months 104 No warranty 105 1 month 106 1 year
db<>fiddle here