3 Replies Latest reply: Feb 22, 2013 7:22 AM by 992621 RSS

    NEED HELP WITH CASE GROUP BY CLAUSE PLEASE

    992621
      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
          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
            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
              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????