PL/SQL Case Statement

Posted on 2006-03-21
Last Modified: 2008-01-09
Hi Guys,

I need to update a column base on values from another column. Lets say I have a table Cust with fields
Sales and Bracket. Sales has the actual values and Bracket is null. I'd like to create a Case statement to the following break down

IF Sales >= 0 and Sales < 150 Then
       Bracket = 1
Else IF Sales >= 150 and Sales < 300 Then
       Bracket  = 2
Else IF Sales >= 300 and Sales < 600 Then
       Bracket = 3
Else IF Sales >= 600 and Sales < 900 Then
       Bracket = 4
Else IF Sales >= 900 Then
       Bracket = 5
End IF;

Thanks so very much for your help!

Question by:JoeSand2005
    LVL 11

    Accepted Solution

    UPDATE cust
          SET bracket = CASE
                                  WHEN sales BETWEEN 0    AND 149 THEN 1
                                  WHEN sales BETWEEN 150 AND 299 THEN 2
                                  WHEN sales BETWEEN 300 AND 599 THEN 3
                                  WHEN sales BETWEEN 600 AND 899 THEN 4
                                  WHEN sales >= 900 THEN 5
                                ELSE null -- or whatever default value you choose

    Hope that helps!

    Author Comment

    Hi Pennnn,

    I tried your code and I got this error message
    ERROR at line 2:
    ORA-00600: internal error code, arguments: [kkmgpvc2], [], [], [], [], [], [],
    Do you have any idea why?


    Author Comment

    Hey Pennnn,

    I got it, problem solve, your code works pretty good. I really appreciate your help


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now