Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    54

    Unanswered: Grouping by a searched case statement

    I am trying to use a searched case statement to evaluate some date, but cannot seem to get the group by statement correct. here is the query so far:

    Code:
    select sub.sub_id, sub.sub_nm, count(sub.sub_id),
    case 
    when ch.sub_id = ch.bill_prnt_sub_id and count(sub.sub_id) = 1
    then 'Stand Alone'
    when ch.sub_id = ch.bill_prnt_sub_id and count(sub.sub_id) > 1
    then 'Parent'
    else 'Child'
    end
    from wasabi.v_sub sub,
    wasabi.v_sub ch
    where sub.sub_id = ch.bill_prnt_sub_id
    and ch.sub_id <> ch.bill_prnt_sub_id
    and ch.stat = 'A'
    
    group by sub.sub_id, sub.sub_nm
    I cannot simply put the case statement in the group by because it includes a sumamry function, and if I add the fields used in the case to the group by, it invalidates the Count() data.

    Any help would be greatly appreciated!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How about such idea? Substitute CASE with DECODE and see what happens:
    Code:
    SELECT sub.sub_id, sub.sub_nm, COUNT(sub.sub_id) cnt,
      DECODE(SIGN(1 - COUNT(sub.sub_id)), 0, 'Stand Alone',
                                         -1, 'Parent',
                                             'Child'
            ) what_is_it
    FROM wasabi.v_sub sub, wasabi.v_sub ch
    WHERE sub.sub_id = ch.bill_prnt_sub_id
      AND ch.sub_id <> ch.bill_prnt_sub_id
      AND ch.stat = 'A'
    GROUP BY sub.sub_id, sub.sub_nm;

  3. #3
    Join Date
    Nov 2003
    Posts
    54
    Quote Originally Posted by Littlefoot
    How about such idea? Substitute CASE with DECODE and see what happens:
    Code:
    SELECT sub.sub_id, sub.sub_nm, COUNT(sub.sub_id) cnt,
      DECODE(SIGN(1 - COUNT(sub.sub_id)), 0, 'Stand Alone',
                                         -1, 'Parent',
                                             'Child'
            ) what_is_it
    FROM wasabi.v_sub sub, wasabi.v_sub ch
    WHERE sub.sub_id = ch.bill_prnt_sub_id
      AND ch.sub_id <> ch.bill_prnt_sub_id
      AND ch.stat = 'A'
    GROUP BY sub.sub_id, sub.sub_nm;
    Your decode does work, but the query ignores the first part of the comparison. The purpose of this query is identify parent child relationships that have 3 possible classifications:

    1. Parent
    2. Child
    3. Stand Alone (A parent without children)

    The sub_id is a customer, and the bill_prnt_sub_id is their parent. Does this make sense?

  4. #4
    Join Date
    Nov 2003
    Posts
    54

    Found a solution

    TO get around the grouping problem, I put the summary calculation in a sub select and that seems to work - here is what I came up with:

    Code:
    select sub.sub_id, 
    case 
    when sub.sub_id = sub.bill_prnt_sub_id and wit.cnt = 1
    then 'Stand Alone'
    when sub.sub_id = sub.bill_prnt_sub_id and wit.cnt > 1
    then 'Parent'
    else 'Child'
    end
    from 
    wasabi.v_sub sub,
    (select sub.sub_id, sub.sub_nm, count(sub.sub_id) cnt
    from wasabi.v_sub sub,
    wasabi.v_sub ch
    where sub.sub_id = ch.bill_prnt_sub_id 
    
    and ch.stat = 'A'
    group by sub.sub_id, sub.sub_nm
    order by sub.sub_id) wit
    where sub.sub_id = wit.sub_id (+)
    
    group by sub.sub_id, case 
    when sub.sub_id = sub.bill_prnt_sub_id and wit.cnt = 1
    then 'Stand Alone'
    when sub.sub_id = sub.bill_prnt_sub_id and wit.cnt > 1
    then 'Parent'
    else 'Child'
    end
    Thanks for your suggestion, it got me moving down the right path.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    that would have been my suggestion.
    good job
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    By "count(sub.sub_id)", do you mean "count(*)"? I'm thnking it could all become clearer if you take out references to sub.sub_id where it isn't needed.

Posting Permissions

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