CODEWITHSUNDEEP
oracle-databaseplsql
user3722544
user3722544

Reputation: 147

Month and quarter conversion

In my database I create month table where the data like this 1M,2M, 6M,1Q, 2Q, 3Q(Here M=month and Q= quarter) But I want to create a function where when I call 1M, 2M, 6M as input the output show just 1,2,6 and when Input are 1Q, 2Q, 3Q the Outputs are 3,6,9.

Upvotes: 0

Views: 866

Answers (4)

pvn joshi
pvn joshi

Reputation: 1

I have an emp table base on which I made this:

SELECT QUARTER,SUM(SAL) FROM (select SAL, CASE 
WHEN to_char(hiredate,'mm') IN (01,02,03) THEN 'Q1'
WHEN to_char(hiredate,'mm') IN (04,05,06) THEN 'Q2'
WHEN to_char(hiredate,'mm') IN (07,08,09) THEN 'Q3'
ELSE 'Q4'
END AS QUARTER
FROM EMP)
GROUP BY QUARTER

Upvotes: 0

MT0
MT0

Reputation: 168741

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE FUNCTION months_and_quarters_to_months (
  in_month_or_quarter VARCHAR2
) RETURN NUMBER DETERMINISTIC
AS
  v_value NUMBER(2,0);
BEGIN
  v_value := TO_NUMBER( SUBSTR( in_month_or_quarter, 1, LENGTH( in_month_or_quarter ) - 1 ) );
  RETURN CASE UPPER( SUBSTR( in_month_or_quarter, -1 ) )
              WHEN 'Q' THEN 3 * v_value
              WHEN 'M' THEN v_value END;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;
/

Query 1:

WITH DATA AS (
  SELECT  COLUMN_VALUE str
  FROM    TABLE(sys.odcivarchar2list('1M', '2M', '12M', '1Q', '2Q', '3Q', null, 'A', 'Q', '4Q', '12m', 'QQ')))
SELECT str,
       months_and_quarters_to_months(str) mq
FROM   DATA

Results:

|    STR |     MQ |
|--------|--------|
|     1M |      1 |
|     2M |      2 |
|    12M |     12 |
|     1Q |      3 |
|     2Q |      6 |
|     3Q |      9 |
| (null) | (null) |
|      A | (null) |
|      Q | (null) |
|     4Q |     12 |
|    12m |     12 |
|     QQ | (null) |

Upvotes: 0

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

Function:

create or replace function mq2num(i_str in varchar2) return number is
  v_ret number;
begin
  if i_str not like '%Q' and i_str not like '%M' then
    return null;
  end if;

  begin
    v_ret := to_number(substr(i_str, 1, length(i_str)-1));
  exception when others then
    return null;
  end;

  if i_str like '%Q' then
    if v_ret not between 1 and 4 then
      return null;
    else
      v_ret := v_ret * 3;
    end if;
  elsif i_str like '%M' then
    if v_ret not between 1 and 12 then
      return null;
    end if;

  else
    return null;
  end if;

  return v_ret;

end mq2num;

Returns null if something is wrong, for instance bad parameter, I don't know what you want in this situation.

Test: 

with t as (
  select column_value str 
    from table(sys.odcivarchar2list('1M', '2M', '12M', '1Q', '2Q', '3Q')))
select str, mq2num(str) mq,
    case 
      when str like '%M' then to_number(substr(str, 1, length(str)-1)) 
      when str like '%Q' then 3*to_number(substr(str, 1, length(str)-1)) 
    end val
  from t

STR            MQ        VAL
------ ---------- ----------
1M              1          1
2M              2          2
12M            12         12
1Q              3          3
2Q              6          6
3Q              9          9

As you see you don't need special function for this, but function is more comfortable.

Upvotes: 1

davegreen100
davegreen100

Reputation: 2115

something like this should do it

create or replace function l_convert (p_input in varchar2) return number is
  l_output number;
begin

  select substr(p_input,1,1) * decode(substr(p_input,2,1),'M',1,'Q',3)
    into l_output
    from dual;

  return l_output;

end;
/

Upvotes: 1

Related Questions