This discussion is archived
2 Replies Latest reply: Feb 21, 2013 2:19 PM by rp0428 RSS

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

992621 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    }

Legend

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