CODEWITHSUNDEEP
sqlviewhsqldb
soul_killer
soul_killer

Reputation: 3

HSQL view ERROR: expression not in aggregate or GROUP BY columns

I have a CREATE VIEW query in hsql, but whenever I run it, it throws me this error:

expression not in aggregate or GROUP BY columns: AGV.ID

I understand that GROUP BY would not work without any aggregate expressions(AVG, SUM, MIN, MAX), but I can't figure out how to fix my query.. because each record needs to be grouped by manifestID value.

Basically, im trying to create a VIEW by combining 3 sets of select queries.

I tried to use distinct but no luck, since it will not work if i have multiple selected columns. This query works fine in MYSQL.

My Query: 

CREATE VIEW local_view_event_manifest(
        manifest_id,
        eventId, 
        eventType, 
        eventDate,
        manifestID, 
        businessStepStr, 
        manifestVersion,
        externalLocation, 
        remark, 
        epcCode, 
        locationCode
        )
        AS
        SELECT 
        agm.manifest_id AS manifest_id,
        agv.id AS eventId,
        'AGGREGATION_EVENT' AS eventType,
        agv.event_time AS eventDate,
        md.manifest_id AS manifestID,
        agv.business_step_code AS businessStepStr,
        md.manifest_version AS manifestVersion,
        md.external_location AS externalLocation,
        md.remark AS remark,
        epc.code as epcCode,
        bloc.location_code as locationCode
        FROM 
        "local".local_MANIFEST_DATA AS md,
        "local".local_AGGREGATION_EVENT AS agv,
        "local".local_AGGREGATION_EVENT_EPCS AS agv_epc,
        "local".local_EPC AS epc,
        "local".local_BUSINESS_LOCATION AS bloc,
        "local".local_AGGREGATION_EVENT_MANIFEST_DATA AS agm
        WHERE
        md.id=agm.manifest_id
        AND agv.deleted=0
        AND md.deleted=0
        AND agv.id=agm.aggregation_event_id
        AND agv.id=agv_epc.aggregation_event_id
        AND agv.business_location_id=bloc.id
        AND bloc.id=agv.business_location_id
        AND agv_epc.epc_id=epc.id
        GROUP BY agm.manifest_id

        UNION
        SELECT 
        om.manifest_id AS manifest_id,
        ov.id AS eventId,
        'OBJECT_EVENT' AS eventType,
        ov.event_time AS eventDate,
        md.manifest_id AS manifestID,
        ov.business_step_code AS businessStepStr,
        md.manifest_version AS manifestVersion,
        md.external_location AS externalLocation,
        md.remark AS remark,
        epc.code as epcCode,
        bloc.location_code as locationCode
        FROM 
        "local".local_MANIFEST_DATA AS md,
        "local".local_OBJECT_EVENT AS ov,
        "local".local_OBJECT_EVENT_EPCS AS ov_epc,
        "local".local_EPC AS epc,
        "local".local_BUSINESS_LOCATION AS bloc,
        "local".local_OBJECT_EVENT_MANIFEST_DATA AS om
        WHERE
        md.id=om.manifest_id
        AND ov.deleted=0
        AND md.deleted=0
        AND ov.id=ov_epc.object_event_id
        AND ov.id=om.object_event_id
        AND bloc.id=ov.business_location_id
        AND ov_epc.epc_id=epc.id
        GROUP BY om.manifest_id

        UNION
        SELECT 
        trm.manifest_id AS manifest_id,
        trv.id AS eventId,
        'TRANSACTION_EVENT' AS eventType,
        trv.event_time AS eventDate,
        md.manifest_id AS manifestID,
        trv.business_step_code AS businessStepStr,
        md.manifest_version AS manifestVersion,
        md.external_location AS externalLocation,
        md.remark AS remark,
        epc.code as epcCode,
        bloc.location_code as locationCode
        FROM 
        "local".local_MANIFEST_DATA AS md,
        "local".local_TRANSACTION_EVENT AS trv,
        "local".local_TRANSACTION_EVENT_EPCS AS trv_epc,
        "local".local_EPC AS epc,
        "local".local_BUSINESS_LOCATION AS bloc,
        "local".local_TRANSACTION_EVENT_MANIFEST_DATA AS trm
        WHERE
        md.id=trm.manifest_id
        AND trv.deleted=0
        AND md.deleted=0
        AND trv.id=trv_epc.transaction_event_id
        AND trv.id=trm.transaction_event_id
        AND bloc.id=trv.business_location_id
        AND trv_epc.epc_id=epc.id
        GROUP BY trm.manifest_id

below are snapshots of query results in mysql using GROUP BY & without GROUP BY:

query result with GROUP BY

query result without GROUP BY

T

@fredt... thanks for the detailed explanation.. referring to your suggestion, i've tried it.. but somehow im getting this error:

ERROR: Table not found: TABL_B in statement [SELECT TABL_B.* FROM (SELECT DISTINCT manifest_id FROM "local".local_AGGREGATION_EVENT_MANIFEST_DATA) TABL_A ] Error Code: -22

Below is my query:

SELECT TABL_B.* FROM (SELECT DISTINCT manifest_id FROM "local".local_AGGREGATION_EVENT_MANIFEST_DATA) TABL_A
LATERAL JOIN 
( SELECT 
    agm.manifest_id AS manifest_id,
    agv.id AS eventId,
    'AGGREGATION_EVENT' AS eventType,
    agv.event_time AS eventDate,
    md.manifest_id AS manifestID,
    agv.business_step_code AS businessStepStr,
    md.manifest_version AS manifestVersion,
    md.external_location AS externalLocation,
    md.remark AS remark,
    epc.code as epcCode,
    bloc.location_code as locationCode
    FROM 
    "local".local_MANIFEST_DATA AS md,
    "local".local_AGGREGATION_EVENT AS agv,
    "local".local_AGGREGATION_EVENT_EPCS AS agv_epc,
    "local".local_EPC AS epc,
    "local".local_BUSINESS_LOCATION AS bloc,
    "local".local_AGGREGATION_EVENT_MANIFEST_DATA AS agm
    WHERE
    md.id=agm.manifest_id
    AND agv.deleted=0
    AND md.deleted=0
    AND agv.id=agm.aggregation_event_id
    AND agv.id=agv_epc.aggregation_event_id
    AND agv.business_location_id=bloc.id
    AND bloc.id=agv.business_location_id
    AND agv_epc.epc_id=epc.id AND manifest_id = TABL_A.manifest_id  LIMIT 1 ) TABL_B

thanks @fredt.. i noticed on the comma thing and already added to my query. and i tried removing the JOIN word also. But still throwing same error..

ERROR: Table not found in statement [SELECT TABL_B.* FROM (SELECT
DISTINCT MANIFEST_ID FROM "local".local_AGGREGATION_EVENT_MANIFEST_DATA)
AS TABL_A, LATERAL] Error Code: -22

Upvotes: 0

Views: 9332

Answers (1)

fredt
fredt

Reputation: 24352

With HSQLDB 2.2.x or later:

When you use GROUP BY, all your selected columns must be in the GROUP BY list, except any column that is an aggregate. In your example, the GROUP BY list should contain 11 columns, not one column.

You can use DISTINCT, as in SELECT DISTINCT COL1, COLB, COLC, ... without group by. DISTINCT is a shortcut for grouping all the columns in the SELECT list.

In general, GROUP BY means the query should return one row only for each combination of column values in the GROUP BY list. Aggregates are allowed as they combine values from possibly multiple rows into one value.

Now if you include all the columns in the GROUP BY list and the result has multiple rows with the same manifestID value, it means you cannot really group on manifestID alone.

Update: Your query results with MySQL show it is not very strict in this area. There are over twenty rows with manifestId=bhbhbhbh and the values in other columns are not the same. Yet MySQL returns one of the rows randomly. This is not how GROUP BY works according to the SQL Standard supported by other databases, including HSQLDB. See this blog by a MySQL expert:

http://www.mysqlperformanceblog.com/2006/09/06/wrong-group-by-makes-your-queries-fragile/

If you want something like the MySQL output, then you need queries like this:

SELECT TABL_B.* FROM (SELECT DISTINCT manifestID FROM "local".local_AGGREGATION_EVENT_MANIFEST_DATA) TABL_A,
LATERAL 
( [YOUR SELECT STATEMENT WITHOUT GROUP BY] AND manifestID = TABL_A.manifestID  LIMIT 1 ) TABL_B

Try with one of the selects in the VIEW first, then apply to the rest.

You can choose which one of the rows is returned by adding an ORDER BY COL_NAME before LIMIT 1.

Upvotes: 2

Related Questions