DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Decode everything else?

  1. #1
    Join Date
    Jul 2004
    Posts
    3

    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
    WHERE
    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 04:56 PM. Reason: CODE tags aren't working

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Code:
    decode(nam_service_plan, 'PLATINUM', '1','DIAMOND','2','GOLD','3','4')
    Jeff Hunter

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