DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004

    Decode everything else?

    Hi everybody,

    I have a table of customers and a table of service plans. The service plans have a hierarchy, like so (from greatest to least): Platinum > Diamond > Gold > (everything else).

    A customer can have several service plans, but I want to select the customer name and the highest-level service plan that customer has. So far, I'm assigning a rank to each record based upon the hierarchy, and then doing a min or max on the rank column depending on how I assigned the rank.

    Like this:

    SELECT customer_id, nam_service_plan
    FROM customer a, service_plan b, cust_serv_xref c,
    (SELECT customer_id, max(decode(nam_service_plan, 'PLATINUM', '1','DIAMOND','2','GOLD','3') as plan_rank
    FROM customer aa, service_plan bb, cust_serv_xref cc
    WHERE aa.customer_id = cc.customer_id
    AND bb.plan_id = cc.plan_id) max_plan
    a.customer_id = c.customer_id
    AND b.plan_id = c.plan_id
    AND a.customer_id = max_plan.customer_id
    AND decode(nam_service_plan, 'PLATINUM', '1','DIAMOND','2','GOLD','3') = max_plan.max_rank

    My question is, how can I group all the other plans into ranking 4? Is there some way to do the decode statement to say, "decode everything else"?

    Or is there a better way to attack this problem?
    Last edited by pie; 12-27-2005 at 03:56 PM. Reason: CODE tags aren't working

  2. #2
    Join Date
    Nov 2000
    decode(nam_service_plan, 'PLATINUM', '1','DIAMOND','2','GOLD','3','4')
    Jeff Hunter
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.