topic Re: LOAD WITH GROUP BY STATMENT GIVES ERROR in QlikView

LOAD WITH GROUP BY STATMENT GIVES ERROR

Mon, 03 Dec 2012 14:31:11 GMT

Dear All,

The following Load statement:

LOAD "SL_LOCN_CODE",

"SL_ITEM_CODE",

"SL_TXN_CODE",

SUM ("SL_QTY") AS TOT_SL_QTY ;

SQL SELECT *

FROM JN."OV_STOCK_LEDGER" WHERE (SL_DT >'31-NOV-2012' AND SL_DT <'01-JAN-2014') GROUP BY SL_LOCN_CODE,SL_ITEM_CODE,SL_TXN_CODE;

gives error

SQL Error:[Microsoft][ODBC driver for Oracle][Oracle]ORA-00979: not a GROUP BY expression

SQL Scriptline:

SQL State:NA000

SQL SELECT *

FROM JN."OV_STOCK_LEDGER" WHERE (SL_DT >'31-NOV-2012' AND SL_DT <'01-JAN-2014') GROUP BY SL_LOCN_CODE,SL_ITEM_CODE,SL_TXN_CODE

Please advise. Thanks

Re: LOAD WITH GROUP BY STATMENT GIVES ERROR

alexpanjhc — Mon, 03 Dec 2012 14:43:33 GMT

your sql is incorrect. You need to combine with an aggregated function such as sum(), max()... on one or more of the fields and then group by the rest of the fields that do not use aggregated functions.

Re: LOAD WITH GROUP BY STATMENT GIVES ERROR

Gysbert_Wassenaar — Mon, 03 Dec 2012 17:08:12 GMT

The group by statement should be with the load statement, but using it with a preceding load isn't the best idea. So try this:

Temp:

SQL SELECT *

FROM JN."OV_STOCK_LEDGER" WHERE (SL_DT >'31-NOV-2012' AND SL_DT <'01-JAN-2014')

Result:

LOAD SL_LOCN_CODE,

SL_ITEM_CODE

SL_TXN_CODE,

SUM (SL_QTY) AS TOT_SL_QTY

RESIDENT Temp GROUP BY SL_LOCN_CODE,SL_ITEM_CODE,SL_TXN_CODE;

drop table Temp;

Re: LOAD WITH GROUP BY STATMENT GIVES ERROR

Tue, 04 Dec 2012 12:30:21 GMT

Dear Gysbert, Thank you. It is working.