Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
Dear Gysbert, Thank you. It is working.