OraFAQ Forum: SQL & PL/SQL » Oracle case statment solution

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle case statment solution

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle case statment solution [message #637128]

Mon, 11 May 2015 05:55

purnima1
Messages: 79
Registered: June 2014

Member

Hi All,
CAn somebody help in simplifying the below mentioned code.Actully if you find we are calculating same thing again and again in when clause and As per my understanding it will calculate the value for all the when clause till the time it satify any of when clause or reaches till else.

1) please correct if my this understanding is wrong.
2) How can i avoid this repetative calculation in when clause again and again.
Thanks in advance

declare 
TRAD_MATURITY_DATE date := '21-feb-2015';
STRT_DATE date :='20-feb-2015';
p_val varchar2(10);
begin 
select 
case   when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )  between 1  and 3 then 'OT01'
  
when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )  between  4 and 7 then 'OT02'
  else 
  'Unknown' end into p_val  from dual ;
  
  dbms_output.put_line(p_val);
  end;

Report message to a moderator

Re: Oracle case statment solution [message #637129 is a reply to message #637128]

Mon, 11 May 2015 06:00

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

purnima1 wrote on Mon, 11 May 2015 16:25

TRAD_MATURITY_DATE date := '21-feb-2015';
STRT_DATE date :='20-feb-2015';

'21-feb-2015' is a string and NOT a DATE.

Regarding helping you with the query, you need to explain in words, what actually you are trying to achieve with the query? Can you post the sample data? Please read how to post a test case.

Report message to a moderator

Re: Oracle case statment solution [message #637130 is a reply to message #637128]

Mon, 11 May 2015 06:33

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

How can i avoid this repetative calculation in when clause again and again.

You can calculate it and store the result in a variable.

And to emphasize what Lalit told you about date:

SQL> declare
  2  TRAD_MATURITY_DATE date;
  3  begin
  4    TRAD_MATURITY_DATE := '21-feb-2015';
  5  end;
  6  /
end;
   *
ERROR at line 5:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 4

Report message to a moderator

Re: Oracle case statment solution [message #637133 is a reply to message #637130]

Mon, 11 May 2015 08:04

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

As it stands the second WHEN in the CASE statement is completely pointless.
1 and 0 can never be between 4 and 7.
You can use nested queries or WITH clauses to avoid doing calculations multiple times.
Or if (as your example implies) you aren't querying the data from anywhere then you can just do the calculation in PL/SQL and store it in a variable as Michel already suggested. If you really are just using dual then I'd just use an IF statement in PL/SQL and skip the query entirely.

Report message to a moderator

Re: Oracle case statment solution [message #637159 is a reply to message #637133]

Mon, 11 May 2015 22:47

purnima1
Messages: 79
Registered: June 2014

Member

Hi, this is just sample dates I have taken. In actual I have one table which has million of records and each row has these two dates trad_maturity_date and start date as column. So for each row these two columns may have different values.I have taken an example.
Let say in below given code if I change the dates value now it will calculate all the when till the time it reaches at 7th when clause where it satisfies.
so i wan to avoid this calculation again and again.
@cookiemonster : Thanks you are right. we can use with clause but actully we are using OWB (oracle warehouse builder tool) . In that i don think we can use with clause.I m wrong please correct me .
Thanks in advance.

declare 
TRAD_MATURITY_DATE date :=to_date('21-feb-2025');
STRT_DATE date :=to_date('20-feb-2015');
p_val varchar2(10);
begin 
select 
case   when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )  between 1  and 3 then 'OT01'
  
when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )  between  4 and 7 then 'OT02'
  
    
when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )  between  8 and 14 then 'OT03'
  
  when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )   between  15 and 29 then 'OT04'
  
    when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )    between  30 and 60 then      'OT05'
  
      when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )    between  61 and 89 then      'OT06'
  
      when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )    between  90 and 182 then      'OT07'
  
      when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )    between  183 and 363 then      'OT08'
  
    
      when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )    between  364 and 730 then      'OT09'
  
        when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )    between  731 and 1823  then      'OT11'
  
        when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )    between  1824 and 3658  then      'OT12'
  
        when  ((extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )+
  (case when extract(DAY from TRAD_MATURITY_DATE )> extract(DAY from  STRT_DATE)  then 1 else 0 end  ) )  >3659 then      'OT13'
  else 
  'Unknown' end  into p_val  
from dual ;
  
  dbms_output.put_line(p_val);
  end;

Report message to a moderator

Re: Oracle case statment solution [message #637160 is a reply to message #637159]

Tue, 12 May 2015 00:07

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/
We have not all the same default format:

SQL> declare
  2  TRAD_MATURITY_DATE date;
  3  begin
  4    TRAD_MATURITY_DATE := to_date('21-feb-2025');
  5  end;
  6  /
TRAD_MATURITY_DATE date;
       *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 4

Always use a format mask with TO_DATE, and remeber we not all speak english, so do NOT use month names unless you specify the date language (have a close look at TO_DATE function).

2/
Use "p_val = ...;" instead of "select ... into p_val from dual;"
You write PL/SQL not SQL here.

3/
First compute the repetitive expression and store it in a variable the use CASE on this variable.

4/
If you can't use WITH (I wonder why you can't use something that is in SQL), then just use an inline view.

Report message to a moderator

Re: Oracle case statment solution [message #637167 is a reply to message #637160]

Tue, 12 May 2015 03:25

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

I'd be very surprised if an oracle tool prevented you from using any standard SQL clause, but if OWB really doesn't allow WITH (I've never used it so I don't know) then use an inline view as Michel suggested.

This:

(extract(YEAR from TRAD_MATURITY_DATE) -  extract(YEAR from  STRT_DATE))*12 + 
  ( extract(MONTH from TRAD_MATURITY_DATE) -extract(MONTH from STRT_DATE) )

Can be simplified to this:

floor(months_between(TRAD_MATURITY_DATE, STRT_DATE))

Report message to a moderator

Previous Topic:	Pivoting Data
Next Topic:	Trigger associated with two Database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Aug 04 05:16:11 CDT 2026