Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

CASE Statement - and checking number ranges

user16854Feb 15 2008 — edited Feb 15 2008

I am attempting to write a bit of SQL that can be used to do a count of Requisition Values, in different value bands, e.g.:

0 - 250
251 - 500
501 - 1000
etc...

I have got this far:

SELECT   sum_value
       , COUNT(req_id) req_ct
    FROM (SELECT   prha.requisition_header_id req_id
                 , CASE SUM(prla.unit_price * prla.quantity)
                      WHEN 250
                         THEN '250'
                      WHEN 500
                         THEN '500'
                      WHEN 500
                         THEN '1000'
                      ELSE 'OTHER'
                   END sum_value
              FROM po.po_requisition_headers_all prha
                 , po.po_requisition_lines_all prla
             WHERE prha.requisition_header_id = prla.requisition_header_id
               AND prha.creation_date >= SYSDATE - 3
          GROUP BY prha.requisition_header_id
                 , prla.requisition_header_id) rt
GROUP BY sum_value;

SUM_V     REQ_CT
----- ----------
250            3
500            6
OTHER        726

But - it is not that great, in that it counts REQs with an exact value of 250,400,500 etc.

I am trying to work out the syntax to allow the CASE statement to work with ranges, but I can't work it out.

I have naively attempted this, for example:

WHEN > 250 AND 500

But that errors out, because I can see that the '>' needs to know what it is checking

Any help much appreciated.

Thank you

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 14 2008
Added on Feb 15 2008
4 comments
1,184 views