LOAD WITH GROUP BY STATMENT GIVES ERROR

[#1]

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

[alexpanjhc]

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.

[Gysbert_Wassenaar]

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;

[#4]

Dear Gysbert, Thank you. It is working.