1. Registered User
Join Date
Aug 2008
Posts
464

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. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713

## 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;```

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

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. Registered User
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
•