Home » SQL & PL/SQL » SQL & PL/SQL » case statment
case statment [message #256100] Fri, 03 August 2007 01:15 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi

i am just doing R&d. i have wrote following query to get data in particular range.

select range,sum(salary) 
from (select case  when EMPLOYEE_ID between 101 and 103 then  '101-103' 
             when EMPLOYEE_ID between 104 and 105 then  '104-105'
             else 'default'end as range,salary 
      from employee) 
group by range 

select range,sum(salary) 
from (select decode(employee_id,
101,'101-103',
102,'101-103',
103,'101-103',
104,'104-105',
105,'104-105',
'DEFAULT') range ,
salary from employee) 
group by range


my question is that how can i use 'between and' statement in decode block.

--Yash



[Updated on: Fri, 03 August 2007 01:43] by Moderator

Report message to a moderator

Re: case statment [message #256101 is a reply to message #256100] Fri, 03 August 2007 01:19 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
sorry michle my second query got 80 charcter long Laughing .

SELECT 
  range,
  sum(salary)
FROM 
  (select decode(employee_id,
  101,
  '101-103',
  102,
  '101-103',
  103,
  '101-103',
  104,
  '104-105',
  105,
  '104-105',
  'DEFAULT') range ,
  salary from employee) group by range 


--Yash
Re: case statment [message #256105 is a reply to message #256100] Fri, 03 August 2007 01:23 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>my question is that how can i use 'between and' statement in decode block.

You can use it well, poorly or not at all. The choice is yours.
Re: case statment [message #256112 is a reply to message #256105] Fri, 03 August 2007 01:31 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
it would be gr8 if you provide the syntax ...
Re: case statment [message #256120 is a reply to message #256112] Fri, 03 August 2007 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no syntax with decode, you have to juggle with sign and subtraction.

Regards
Michel
Re: case statment [message #256121 is a reply to message #256112] Fri, 03 August 2007 01:45 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ammishra wrote on Fri, 03 August 2007 08:31
it would be gr8 if you provide the syntax ...

It would be gREAT indeed if one would use correct spelling... Wink

MHE
Re: case statment [message #256125 is a reply to message #256121] Fri, 03 August 2007 01:56 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Ok Maaher !! before clicking the submit button..i will try to read my post properly ..but I know you can manage Cool

Re: case statment [message #256133 is a reply to message #256125] Fri, 03 August 2007 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before clicking on the "Submit Reply", click on "Spell-check Message".

Regards
Michel
Re: case statment [message #256146 is a reply to message #256133] Fri, 03 August 2007 02:57 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thank you sir Michel !!
Re: case statment [message #256154 is a reply to message #256146] Fri, 03 August 2007 03:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
To answer your question, I would use a range table with:
1. lower bound
2. upper bound
3. range name

And I would join that with your table:

SELECT <columns>
     , range_table.range_name
FROM   <your_table>
   ,   range_table
WHERE  <your_table>.<your_column> BETWEEN range_table.lower_bound
                                  AND     range_table.upper_bound;


MHE

[Updated on: Fri, 03 August 2007 03:50]

Report message to a moderator

Re: case statment [message #256156 is a reply to message #256154] Fri, 03 August 2007 03:55 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
It is ok..i am not asking for alternate option. I am asking how can we use 'BETWEEN AND' statement inside decode block..
Re: case statment [message #256158 is a reply to message #256156] Fri, 03 August 2007 04:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is no BETWEEN syntax for Decode.
CASE would be the expression to use for that.

You can (if you're on 8i, or just feeling masochistic) simulate a between with atwo level nested decode and the SIGN operator;
with tbl as (select 1 col from dual union all
             select 2 col from dual union all
             select 3 col from dual union all
             select 4 col from dual union all
             select 5 col from dual union all
             select 6 col from dual union all
             select 7 col from dual union all
             select 8 col from dual union all
             select 9 col from dual union all
             select 0 col from dual)
select col
      ,case when col between 3 and 6 then '3-6' else 'Out of range' end
      ,decode(sign(col-3),-1,'Out of range',decode(sign(col-6),1,'Out of range','3-6'))
from tbl
but before you do this, ask yourself which one of these will take you less work to understand when you come back to maintain the code in two years time.
Re: case statment [message #256160 is a reply to message #256156] Fri, 03 August 2007 04:10 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It will take a little bit of creativity. Michel has given a hint. Did you consider it?

Well, enough talk. Does this help?
SELECT e.employee_id
     , DECODE(SIGN(employee_id-104)
             ,-1, '101-103'
             , DECODE(SIGN(employee_id-106)
                     ,-1,'104-105'
                     , 'Default'
                     )
            ) blah
FROM   employees e
/


MHE
Previous Topic: Replacing a particular charater in the data being displayed
Next Topic: Mutating error
Goto Forum:
  


Current Time: Wed Dec 07 06:50:51 CST 2016

Total time taken to generate the page: 0.14443 seconds