CODEWITHSUNDEEP
oracle-databaseoracle11g
Jony_Junior
Jony_Junior

Reputation: 35

Filter column interval year to month

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:

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

Upvotes: 0

Views: 66

Answers (1)

MT0
MT0

Reputation: 167822

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

Upvotes: 2

Related Questions