This discussion is archived
3 Replies Latest reply: Feb 22, 2013 5:22 AM by 992621 RSS

NEED HELP WITH CASE GROUP BY CLAUSE PLEASE

992621 Newbie
Currently Being Moderated
Thank you User1983440 for the assistance with the script below grouped by Product Category, can you or someone else help me group by the other Case statment as well.


WITH p AS (
Select distinct
partner, region, od.ORDER_AFFILIATE_NAME,
oh.external_order_number,
ain.get_g1(queue_type) offer,
Case
When disp.category = 100 Then'ORDER'
When disp.category = 200 Then'ACCESS'
When disp.category = 300 Then'DSL'
When disp.category = 600 Then'IPTV'
When disp.category = 700 Then'INTERNET'
When disp.category = 800 Then'VOIP'
End as product_category,
TO_CHAR(oh.created_ts, 'yyyy-mm-dd hh24:mi:ss') CREATE_DATE,
Case when ip.offer_type = 'IPDSLAM' or instr(ip.display_name, '- D')> 0
THEN 'IP-DSL'
ELSE 'FTTN'
end as product_type,
Case
When disp.state = 4 or disp.state = 9 or disp.state= 21 or disp.state= 22 Then 'Canceled'
When disp.state = 17 Then 'Canceled NRFC'
When disp.state = 7 Then 'Incomplete'
When disp.state = 3 or disp.state = 8 Then 'Complete'
When disp.state = 6 or disp.state = 10 or disp.state = 13 or disp.state =14 Then 'Pending - Other'
When disp.state = 19 or disp.state = 20 Then 'Submitted'
when disp.state = 21 or disp.state = 22 then 'Cancelled- CSUS'
when disp.state =23 or disp.state = 24 then 'Order Confirmation'
when disp.state =1 or disp.state = 2 or disp.state = 15 then 'Received'
Else 'Other'
End as STATE_desc
from AIN.impl_oh_order_header oh,
ain.impl_order_data od,
AIN.sncr_order_curr_disp disp ,
AIN.impl_package ip
where oh.created_ts between to_date('2013-02-04', 'yyyy-mm-dd')
and to_date('2013-02-11', 'yyyy-mm-dd')
and oh.order_type = 'ORDER'
and disp.category in (200, 300, 600, 700, 800)
and disp.state !='5'
and uscs= 0
and disp.transaction_id = oh.transaction_id
and od. transaction_id = oh.transaction_id
and ip.transaction_id = oh.transaction_id
and ip.package_type = 'PACKAGE'
and ip.parent_package_id is null
and ip.product_type = 'ORDER'
)
SELECT product_category,
COUNT(*)
FROM p
GROUP BY product_category
  • 1. Re: NEED HELP WITH CASE GROUP BY CLAUSE PLEASE
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: NEED HELP WITH CASE GROUP BY CLAUSE PLEASE
    user1983440 Explorer
    Currently Being Moderated
    To summarize by product category, then by state_desc :
    WITH p AS (
    Select distinct
    partner, region, od.ORDER_AFFILIATE_NAME,
    oh.external_order_number,
    ain.get_g1(queue_type) offer,
    Case
    When disp.category = 100 Then'ORDER'
    When disp.category = 200 Then'ACCESS'
    When disp.category = 300 Then'DSL'
    When disp.category = 600 Then'IPTV'
    When disp.category = 700 Then'INTERNET'
    When disp.category = 800 Then'VOIP'
    End as product_category,
    TO_CHAR(oh.created_ts, 'yyyy-mm-dd hh24:mi:ss') CREATE_DATE,
    Case when ip.offer_type = 'IPDSLAM' or instr(ip.display_name, '- D')> 0 
    THEN 'IP-DSL' 
    ELSE 'FTTN' 
    end as product_type,
    Case 
    When disp.state = 4 or disp.state = 9 or disp.state= 21 or disp.state= 22 Then 'Canceled' 
    When disp.state = 17 Then 'Canceled NRFC' 
    When disp.state = 7 Then 'Incomplete' 
    When disp.state = 3 or disp.state = 8 Then 'Complete' 
    When disp.state = 6 or disp.state = 10 or disp.state = 13 or disp.state =14 Then 'Pending - Other' 
    When disp.state = 19 or disp.state = 20 Then 'Submitted' 
    when disp.state = 21 or disp.state = 22 then 'Cancelled- CSUS' 
    when disp.state =23 or disp.state = 24 then 'Order Confirmation' 
    when disp.state =1 or disp.state = 2 or disp.state = 15 then 'Received' 
    Else 'Other' 
    End as STATE_desc 
    from AIN.impl_oh_order_header oh, 
    ain.impl_order_data od, 
    AIN.sncr_order_curr_disp disp ,
    AIN.impl_package ip
    where oh.created_ts between to_date('2013-02-04', 'yyyy-mm-dd') 
    and to_date('2013-02-11', 'yyyy-mm-dd')
    and oh.order_type = 'ORDER'
    and disp.category in (200, 300, 600, 700, 800)
    and disp.state !='5'
    and uscs= 0
    and disp.transaction_id = oh.transaction_id
    and od. transaction_id = oh.transaction_id
    and ip.transaction_id = oh.transaction_id 
    and ip.package_type = 'PACKAGE' 
    and ip.parent_package_id is null 
    and ip.product_type = 'ORDER'
    )
    SELECT product_category, STATE_desc,
    COUNT(*)
    FROM p
    GROUP BY product_category, STATE_desc
    To summarize by state_desc, just remove "product category," from the query above.
  • 3. Re: NEED HELP WITH CASE GROUP BY CLAUSE PLEASE
    992621 Newbie
    Currently Being Moderated
    My fault just found out I need all the columns as part of the group by , including : Case when ip.offer_type = 'IPDSLAM' or instr(ip.display_name, '- D')> 0
    THEN 'IP-DSL'
    ELSE 'FTTN'
    end as product_type,

    Thank you for all of your help it has been perfect, can you help me with the above too please????

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points