i have column it`s look like that https://i.stack.imgur.com/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

CodePudding user response：

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:

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:

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:

fiddle