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

    Unanswered: Applying a different formula....

    Hi guys

    I have a query which returns data for 8 BSCs:

    Code:
    select datetime,   
    (case when moid='2.3.1.1.401' then '2.3.1.1.401 BSC1_RX/TX(P)' 
    when moid='2.4.1.1.411' then '2.4.1.1.411 BSC1_RX/TX(S)' 
    when moid='2.3.3.1.402' then '2.3.3.1.402 BSC2_RX/TX(P)' 
    when moid='2.4.3.1.412' then '2.4.3.1.412 BSC2_RX/TX(S)'
    when moid='2.5.1.1.403' then '2.5.1.1.403 BSC3_RX/TX(P)' 
    when moid='2.6.1.1.413' then '2.6.1.1.413 BSC3_RX/TX(S)'
    when moid='2.5.3.1.404' then '2.5.3.1.404 BSC4_RX/TX(P)'
    when moid='2.6.3.1.414' then '2.6.3.1.414 BSC4_RX/TX(S)'
    when moid='2.7.1.1.405' then '2.7.1.1.405 BSC5_RX/TX(P)' 
    when moid='2.4.8.1.415' then '2.4.8.1.415 BSC5_RX/TX(S)'
    when moid='2.7.3.1.406' then '2.7.3.1.406 BSC6_RX/TX(P)' 
    when moid='2.5.8.1.416' then '2.5.8.1.416 BSC6_RX/TX(S)'
    when moid='3.2.1.1.407' then '3.2.1.1.407 BSC7_RX/TX(P)'
    when moid='3.3.1.1.417' then '3.3.1.1.417 BSC7_RX/TX(S)'
    when moid='3.2.3.1.408' then '3.2.3.1.408 BSC8_RX/TX(P)'
    when moid='3.3.3.1.418' then '3.3.3.1.418 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 ('2.3.1.1.401','2.4.1.1.411','2.3.3.1.402','2.4.3.1.412','2.5.1.1.403','2.6.1.1.413','2.5.3.1.404','2.6.3.1.414','2.7.1.1.405','2.4.8.1.415','2.7.3.1.406','2.5.8.1.416','3.2.1.1.407','3.3.1.1.417','3.2.3.1.408','3.3.3.1.418')
    and datetime between trunc(sysdate)-1 and trunc(sysdate)-1/24
    order by moid, datetime
    Question:
    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
    Location
    Baghdad, Iraq
    Posts
    697
    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:

    Code:
    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:

    Code:
    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:

    Code:
    create function rxtx(moid varchar2, parama number, paramb number)
    returns number as
    begin
       return case moid when '100.200.3'
       then round(parama * 120, paramb)
       when ...
       then ...
       end;
    end;
    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
    Location
    Olympia, WA
    Posts
    337
    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
  •