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

    Unanswered: question updated

    Hi guys

    I have the following query which I'm trying to get to work:

    Code:
    select A.BSC,
             A.CELL, 
             MIN(A.DATETIME) DATETIME, 
             SUM(NVL(ALLOC_S,0)) AS ALLOC_S,
             ROUND(DIV(NVL(ALLOC_S, 0),(NVL(ALLOC_S, 0) + NVL(ALLOC_S_F, 0))), 2) * ROUND(DIV(NVL(ALLOC_T, 0),(NVL(ALLOC_T, 0) + NVL(ALLOC_T_F, 0))), 2)*(1 - ROUND(DIV((NVL(RF_L, 0)),(NVL(ALLOC_S, 0) - NVL(CH_R, 0))), 2))*100 as CSSR_NEW
    
    FROM SCHEMA.TABLE A,
    
    GROUP BY A.BSC,A.CELL
    The problem is I keep getting a message, in short, the last queried expression is not a group by expression which is understandable. But I don't want to group by:
    Code:
    ROUND(DIV(NVL(ALLOC_S, 0),(NVL(ALLOC_S, 0) + NVL(ALLOC_S_F, 0))), 2) * ROUND(DIV(NVL(ALLOC_T, 0),(NVL(ALLOC_T, 0) + NVL(ALLOC_T_F, 0))), 2)*(1 - ROUND(DIV((NVL(RF_L, 0)),(NVL(ALLOC_S, 0) - NVL(CH_R, 0))), 2))*100
    Can I amend the last expression somehow keeping the validity of the equation?

    Regards
    Shajju
    Last edited by shajju; 11-08-09 at 04:07. Reason: Correct question

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool MAX() or Group by?

    You could try these:
    Code:
    --
    -- One solution is to use MAX() function
    --
    SELECT   a.bsc, a.cell, MIN (a.datetime) datetime,
             SUM (NVL (alloc_s, 0)) AS alloc_s,
    	 MAX (
               ROUND (div (NVL (alloc_s, 0),(NVL (alloc_s, 0) + NVL (alloc_s_f, 0))),2)
             * ROUND (div (NVL (alloc_t, 0),(NVL (alloc_t, 0) + NVL (alloc_t_f, 0))),2)
             * ( 1 - ROUND (div ((NVL (rf_l, 0)), (NVL (alloc_s, 0) - NVL (ch_r, 0))),2))
             * 100) AS cssr_new
        FROM SCHEMA.mytable a
    GROUP BY a.bsc, a.cell;
    --
    -- Another is to add that column to the GROUP BY:
    --
    SELECT   a.bsc, a.cell, MIN (a.datetime) datetime,
             SUM (NVL (alloc_s, 0)) AS alloc_s,
               ROUND (div (NVL (alloc_s, 0),(NVL (alloc_s, 0) + NVL (alloc_s_f, 0))),2)
             * ROUND (div (NVL (alloc_t, 0),(NVL (alloc_t, 0) + NVL (alloc_t_f, 0))),2)
             * ( 1 - ROUND (div ((NVL (rf_l, 0)), (NVL (alloc_s, 0) - NVL (ch_r, 0))),2))
             * 100 AS cssr_new
        FROM SCHEMA.mytable a
    GROUP BY a.bsc, a.cell,
               ROUND (div (NVL (alloc_s, 0),(NVL (alloc_s, 0) + NVL (alloc_s_f, 0))),2)
             * ROUND (div (NVL (alloc_t, 0),(NVL (alloc_t, 0) + NVL (alloc_t_f, 0))),2)
             * ( 1 - ROUND (div ((NVL (rf_l, 0)), (NVL (alloc_s, 0) - NVL (ch_r, 0))),2))
             * 100;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    In my opinion you should not concentrate on how to get rid of some error message, but how to achieve the desired result.

    In your case, imagine this content of your table:
    Code:
    BSC CELL CSSR_NEW
    --- ---- --------
    1   1    5
    1   1    7
    1   1    2
    As you group by BSC and CELL, the resultset will contain one row. The question is, what shall be the value (only one) of CSSR_NEW? After you will find it out (as your post does not contain any clue about it), just use proper aggregate function.

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    OK, thanks for the advice...Will work on it......

    Off Topic question:

    Would you know where I can get some help on SNMP? I only have one question regarding OIDs. How to identify between scalar and tabular OIDs in a MIB file? I've searched the Internet so much but no one talks about this. They only explain what scalar and tabular MIBs are.

    Thanks.

Posting Permissions

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