Toad World® Forums

Sub-counters

Hi,

I have this counter that has an array of separate sub-counters where each sub-counter measures a different variable.

Here is the error I get when I try executing the query.

[Sybase][ODBC Driver][Sybase IQ] Function or column reference to ‘DCVECTOR_INDEX’ must also appear in a GROUP

And here is the query.

***SELECT DC_E_ERBS_EUTRANCELLFDD_V_RAW.DATE_ID ***
***, DC_E_ERBS_EUTRANCELLFDD_V_RAW.ERBS ***
***, DC_E_ERBS_EUTRANCELLFDD_V_RAW.EUtranCellFDD ***
, CASE WHEN DC_E_ERBS_EUTRANCELLFDD_V_RAW.DCVECTOR_INDEX = 1 THEN SUM( DC_E_ERBS_EUTRANCELLFDD_V_RAW.pmErabRelAbnormalEnbActQci ) END AS TEST
FROM dc.DC_E_ERBS_EUTRANCELLFDD_V_RAW DC_E_ERBS_EUTRANCELLFDD_V_RAW
WHERE ( “DC_E_ERBS_EUTRANCELLFDD_V_RAW”.“DATE_ID” = ‘2016-1-22’)
AND ( “DC_E_ERBS_EUTRANCELLFDD_V_RAW”.“ERBS” = ‘GNL02214’)
***GROUP BY DC_E_ERBS_EUTRANCELLFDD_V_RAW.DATE_ID ***
***, DC_E_ERBS_EUTRANCELLFDD_V_RAW.ERBS ***
, DC_E_ERBS_EUTRANCELLFDD_V_RAW.EUtranCellFDD.

Adding ***‘DCVECTOR_INDEX’ *** in a group and/or filtering specific sub-counters (example 2) resolves the error but this will generate all the sub-counters values and mess up the table as seen below

DATE_ID
ERBS
EUtranCellFDD
TEST
DCVECTOR_INDEX
1/22/2016
GNL02214
GNL02214_2C_1
81
1
1/22/2016
GNL02214
GNL02214_7A_1
80
1
1/22/2016
GNL02214
GNL02214_7C_1
{null}
5
1/22/2016
GNL02214
GNL02214_2A_1
62
1
1/22/2016
GNL02214
GNL02214_7B_1
{null}
5
1/22/2016
GNL02214
GNL02214_2B_1
{null}
5
1/22/2016
GNL02214
GNL02214_2A_1
{null}
5
1/22/2016
GNL02214
GNL02214_7B_1
60
1
1/22/2016
GNL02214
GNL02214_2C_1
{null}
5
1/22/2016
GNL02214
GNL02214_2B_1
47
1
1/22/2016
GNL02214
GNL02214_7C_1
166
1
1/22/2016
GNL02214
GNL02214_7A_1
{null}
5

Is there a workaround on where I do not need to add ***‘DCVECTOR_INDEX’ *** in a group and/or create a formula per sub-counters?

Thank you!!!

You could use a subquery like in the following example

SELECT Sub_Query.DATE_ID

, Sub_Query.ERBS

, Sub_Query.EUtranCellFDD

, SUM( DC_E_ERBS_EUTRANCELLFDD_V_RAW.pmErabRelAbnormalEnbActQci ) AS TEST

FROM

(SELECT *

FROM dc.DC_E_ERBS_EUTRANCELLFDD_V_RAW DC_E_ERBS_EUTRANCELLFDD_V_RAW

WHERE ( “DC_E_ERBS_EUTRANCELLFDD_V_RAW”.“DATE_ID” = ‘2016-1-22’)

AND ( “DC_E_ERBS_EUTRANCELLFDD_V_RAW”.“ERBS” = ‘GNL02214’

AND DC_E_ERBS_EUTRANCELLFDD_V_RAW.DCVECTOR_INDEX = 1 ) ) Sub_Query

GROUP BY Sub_Query.DATE_ID

, Sub_Query.ERBS

, Sub_Query.EUtranCellFDD;

Regards

Aleksey

Try Suming the case statement: SUM(CASE WHEN DC_E_ERBS_EUTRANCELLFDD_V_RAW.DCVECTOR_INDEX = 1 THEN DC_E_ERBS_EUTRANCELLFDD_V_RAW.pmErabRelAbnormalEnbActQci Else 0) END AS TEST