Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    54

    Question Unanswered: SQL error in Oracle

    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?

  2. #2
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    Quote Originally Posted by heprox
    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
    Thanks and Regards,

    Praveen Pulikunnu

  3. #3
    Join Date
    Oct 2003
    Posts
    54
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •