2 Replies Latest reply: Feb 21, 2013 4:19 PM by rp0428 RSS

    Please Help!!!!!! Need to Change this Script to Do A Count (*)

    992621
      Here is a query that I would like you to change so it does a count(*) instead of listing the detail


      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'
        • 1. Re: Please Help!!!!!! Need to Change this Script to Do A Count (*)
          user1983440
          Try this:

          SELECT COUNT(*) FROM (
          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'
          )



          ...you could also try this:


          WITH x 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 COUNT(*) FROM x;

          Edited by: user1983440 on Feb 21, 2013 5:01 PM

          Edited by: user1983440 on Feb 21, 2013 5:02 PM
          • 2. Re: Please Help!!!!!! Need to Change this Script to Do A Count (*)
            rp0428
            Welcome to the forum!

            Whenever you post provide your 4 digit Oracle version.

            And please don't post duplicate threads. Mark your other thread ANSWERED.
            HELP!!! Need to change this statement to a Count(*)
            >
            Here is a query that I would like you to change so it does a count(*) instead of listing the detail
            >
            You don't need to change the query. Just wrap it in a new one that counts the rows from the query you have now.
            SELECT COUNT(*) FROM (
              -- put your current query in here between the parentheses
            }