Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008

    Unanswered: Applying a different formula....

    Hi guys

    I have a query which returns data for 8 BSCs:

    select datetime,   
    (case when moid='' then ' BSC1_RX/TX(P)' 
    when moid='' then ' BSC1_RX/TX(S)' 
    when moid='' then ' BSC2_RX/TX(P)' 
    when moid='' then ' BSC2_RX/TX(S)'
    when moid='' then ' BSC3_RX/TX(P)' 
    when moid='' then ' BSC3_RX/TX(S)'
    when moid='' then ' BSC4_RX/TX(P)'
    when moid='' then ' BSC4_RX/TX(S)'
    when moid='' then ' BSC5_RX/TX(P)' 
    when moid='' then ' BSC5_RX/TX(S)'
    when moid='' then ' BSC6_RX/TX(P)' 
    when moid='' then ' BSC6_RX/TX(S)'
    when moid='' then ' BSC7_RX/TX(P)'
    when moid='' then ' BSC7_RX/TX(S)'
    when moid='' then ' BSC8_RX/TX(P)'
    when moid='' then ' BSC8_RX/TX(S)'
      end) as moid,frpvcrxbytes,frpvctxbytes,
      round(((FRPVCRXBYTES*8)/1000)/3600,5) "RX_Kb/Sec", 
    round(((((FRPVCRXBYTES*8)/1000)/3600)/(1152))*100,5) "RX_UTILIZATION%",
    round(((FRPVCTXBYTES*8)/1000)/3600,5) "TX_Kb/Sec", 
    round(((((FRPVCTXBYTES*8)/1000)/3600)/(1152))*100,5) "TX_UTILIZATION%"
    from ericsson_gprs.sgsn_framerelay_raw
    where MOID IN ('','','','','','','','','','','','','','','','')
    and datetime between trunc(sysdate)-1 and trunc(sysdate)-1/24
    order by moid, datetime
    I need to use a different formula to calculate 'RX_UTILIZATION%' for BSCs 3, 4 and 8 which means I need to split the above query such that one query will contain data for BSCs 1,2,5,6,7 (and the respective formula to calcuate RX_UTILIXATION) and the other query will contain data for BSCs 3,4,8 (with its own formula for RX_UTILIZATION).

    Could you please give me a hint on how the logic will be applied? I wrote 2 separate query for the above mentioned 2 sets of BSCs but how can I join them?

  2. #2
    Join Date
    Oct 2002
    Baghdad, Iraq
    Have you considered using lookup tables rather than trying to create these complicated case statements?

    Remember, you can go into Enterprise Manager and set the lookup table to be fully cached in memory, and the optimizer is heavily tuned to make lookup tables more efficient.

    If you can't move logic like this into the relational structure, you'd be better off expressing it as a user-defined function. That way you can reuse it across queries.

    Anyway, as to how the logic will be applied: it depends. In the case of a lookup table, say with columns moid, parama, paramb, paramc, it would look like this:

    select a, b, moid, (select round(paramaa * paramb, paramc) from lookup where lookup.moid = framerelay_raw.moid) as rxtx_1
    from framerelay_raw
    Generally, this gets handled as a regular join against a small table. It's also *far* more scalable: if you wind up with thousands of rules, this will continue to work.

    The CASE equivalent is this:

    select a, b, moid, case moid when 1 then round(1 * 2, 3) when 2 then round(1 * 3, 4) when 3 then round(5 * 6, 9) end as rxtx_1
    from framerelay_raw
    Seriously, I'd only do the case version if you're getting paid by the line. If you *must* do the case version, consider:

    create function rxtx(moid varchar2, parama number, paramb number)
    returns number as
       return case moid when '100.200.3'
       then round(parama * 120, paramb)
       when ...
       then ...
    Now you can just say rxtx(moid, 10, 5) anywhere in any query.

    One other note:

    ((x/y)/z) is guaranteed to be equivalent to x/y/z. Division is always left to right, just the way you'd expect. And multiplication and division have the same precedence, so ((((a*b)/c)/d)*e) is the same as a*b/c/d*e.

  3. #3
    Join Date
    Aug 2009
    Olympia, WA
    I agree with what sco08y just said.

    However to answer your original question, UNION ALL is what you are looking for.

Posting Permissions

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