Filter column interval year to month

Question

i have column it`s look like that https://i.sstatic.net/j7Nwh.png it is format +Year-Month an i need to extract some information for example:

• If the period is 0 return "No Warranty"
• if the period is more than 0 return number year number months ...

any suggestion how to approach, I know I have to use some Case or IF Else structure

Answer 1

Use a CASE expression:

SELECT warranty_period,
       CASE warranty_period
       WHEN INTERVAL '0-0' YEAR TO MONTH
       THEN 'No warranty'
       ELSE TO_CHAR(warranty_period)
       END AS descr
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (warranty_period) AS
SELECT INTERVAL '0-0' YEAR TO MONTH FROM DUAL UNION ALL
SELECT INTERVAL '0-1' YEAR TO MONTH FROM DUAL UNION ALL
SELECT INTERVAL '1-0' YEAR TO MONTH FROM DUAL UNION ALL
SELECT INTERVAL '1-2' YEAR TO MONTH FROM DUAL;

Outputs:

WARRANTY_PERIOD	DESCR
+00-00	No warranty
+00-01	+00-01
+01-00	+01-00
+01-02	+01-02

Or, if you want a different format then EXTRACT the component parts:

SELECT warranty_period,
       CASE warranty_period
       WHEN INTERVAL '0-0' YEAR TO MONTH
       THEN 'No warranty'
       ELSE EXTRACT(YEAR FROM warranty_period) || 'y ' || EXTRACT(MONTH FROM warranty_period) || 'm'
       END AS descr
FROM   table_name

Which outputs:

WARRANTY_PERIOD	DESCR
+00-00	No warranty
+00-01	0y 1m
+01-00	1y 0m
+01-02	1y 2m

or:

SELECT warranty_period,
       CASE 
       WHEN warranty_period <= INTERVAL '0-0' YEAR TO MONTH
       THEN 'No warranty'
       WHEN warranty_period < INTERVAL '1-0' YEAR TO MONTH
       THEN EXTRACT(MONTH FROM warranty_period) || ' months'
       WHEN EXTRACT(MONTH FROM warranty_period) = 0
       THEN EXTRACT(YEAR FROM warranty_period) || ' years'
       ELSE EXTRACT(YEAR FROM warranty_period) || ' years and ' || EXTRACT(MONTH FROM warranty_period) || ' months'
       END AS descr
FROM   table_name

Outputs:

WARRANTY_PERIOD	DESCR
+00-00	No warranty
+00-01	1 months
+01-00	1 years
+01-02	1 years and 2 months

fiddle