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

By using this site, you agree to the Privacy Policy