We are building a page in Dreamweaver MX 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_p rc.ret_prc,gm_prc.prev_perm_ret,
store_cd,gm_itm_udf.udf012,div.des,gm_itm.vsn,gm_i tm.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_p rc.ret_prc,gm_prc.prev_perm_ret,
store_cd,gm_itm_udf.udf012,div.des,gm_itm.vsn,gm_i tm.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:

ORA-01013: user requested cancel of current operation

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

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?

Try to use execute immediate for running the query

I finally figured it out, MX was (for some reason?) not liking this part:

"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"

...where all the joins where happening. This statement works fine in Toad, but not in MX. I got rid of one of the two joins to the "gm_itm_udf" table, and made the other an outer join and it ran fine in MX, like this:

"AND gm_prc.itm_cd = gm_itm.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(+)"


...strange but it works an we have verified the output...