Oracle error...

[heprox]

We are building a page with a dynamic table. The page is accessing an Oracle 8i database via ODBC. The SQL statement we are using is:

SELECT /*+ INDEX (gm_inv_loc GM_INV_LOC2)*/
gm_prc.itm_cd,gm_prc.prc_zone_cd,
gm_prc.prc_grp_cd,gm_prc.beg_dt,gm_prc.pc_num,gm_prc.ret_prc,gm_prc.prev_perm_ret,
store_cd,gm_itm_udf.udf012,div.des,gm_itm.vsn,gm_itm.des1,stat_cd, SUM(gm_inv_loc.avail_qty)
FROM gm_prc,gm_inv_loc,gm_itm,gm_itm_udf,div
WHERE store_cd = 'varStore'
AND stat_cd = 'varStatus'
AND gm_prc.pc_tp = 'varType'
AND (beg_dt >= 'varStartDate'
AND beg_dt <= 'varEndDate')
AND gm_prc.itm_cd = gm_itm.itm_cd
AND gm_prc.itm_cd = gm_itm_udf.itm_cd
AND gm_itm.div_cd = div.div_cd
AND gm_prc.sku_num = gm_inv_loc.sku_num
AND gm_itm.itm_cd = gm_itm_udf.itm_cd
GROUP BY gm_prc.itm_cd,gm_prc.prc_zone_cd,
gm_prc.prc_grp_cd,gm_prc.beg_dt,gm_prc.pc_num,gm_prc.ret_prc,gm_prc.prev_perm_ret,
store_cd,gm_itm_udf.udf012,div.des,gm_itm.vsn,gm_itm.des1,stat_cd
HAVING SUM (gm_inv_loc.avail_qty) > 0
ORDER BY gm_inv_loc.store_cd,div.des,gm_prc.ret_prc,gm_itm_udf.udf012,gm_itm.vsn,gm_prc.itm_cd

...this statment returns the error:

[Oracle][ODBC][ORA]ORA-01013: user requested cancel of current operation

...when in the "Recordset" window and then returns the error code:

MM_ERROR:[Oracle][ODBC][Ora]ORA-00937: not a single-group group function

..whenever I attempt to add a dynamic table to the statement. The statement works fine in TOAD, or WinSQL, so I know the issue is not with the ODBC interface or the actual statment itself, but rather somehting in the way that Dreamweaver is handling the statement. Is it possible that MX can't handle hash joins?

 

[lebisol]

DW should still provide a "view" of the recordset....can u write down your SQL in the DB vs. in the ASP page to test it...
but a goole said:
============
Oracle Error 937: ORA-00937: not a single-group group function.

You have added the sum function to an item on the request line twice. Remove the sum function from the item. Query Menu Bar ==> Data Function ==> None and then add it back Query Menu Bar ==> Data Function ==> Sum
==============

http://www.google.com/search?hl=en&...not+a+single-group+group+function&btnG=Search

All the best!

> need more info?
:: don't click HERE ::