"SELECT GREATEST(ROUND((SUM(mth_01 + mth_02 + mth_03 + mth_04 + mth_05
+ + mth_06 + mth_07 + mth_08 + mth_09 + mth_10 + mth_11 + mth_12) / 36
+5 ) * 120 ), ROUND (SUM (mth_08 + mth_09 + mth_10 + mth_11)))  QTY, b
+. p_dlrnet, b.p_dlrsplrcode   
  FROM pac.historyreportmonthlybysku a
              , stsc.sku b
          WHERE a.loc                  = ?
          AND a.dmdunit                = ?
          AND a.loc                    = b.loc
          AND a.dmdunit                = b.ITEM
          GROUP BY a.loc
                  , a.dmdunit
                  , b.p_dlrnet
                  , b.p_dlrsplrcode"
[download]

Maybe it's the difference between the libraries that DBD::Oracle was compiled against vs. the libraries that TOAD was compiled against (I assume you did not compile TOAD anyway)? Someone said there was a 30 character limit, but even that long table name is not 30 characters long. I would start by pasting that SQL as is into a DBI prepare statement and see if it works. Then I might edit the SQL to see if I can narrow down the problem (e.g. does a 'select * from that_long_table_name' work?).

Update:(I also might try calling Oracle tech support, though I don't know if they support perl...they didn't when I last did Oracle...you might have to come up with an OCI example to submit if it comes to that).

Good luck.

You mean you have a table with columns mth_01, mth_02, ... mth_12? Looks like a perfect example of an incorrectly designed table to me. Let's suppose the pac.historyreportmonthlybysku had columns id,loc,dmdunit (primary key is the id) and there was a table pac.historyreportmonthlybyskuval with columns id,mth,qty (where mth is the number of month and qty is the value for that month, primary key is (id,mth)):

SELECT GREATEST(
     ROUND(SELECT SUM(qty) FROM pac.historyreportmonthlybyskuval as hv
+ WHERE hv.id = h.id),
     ROUND(SELECT SUM(qty) FROM pac.historyreportmonthlybyskuval as hv
+ WHERE hv.id = h.id and mth >= ? and mth <= 11)
    ) QTY,
    b.p_dlrnet, b.p_dlrsplrcode   
  FROM pac.historyreportmonthlybysku h -- at least use meaningfull let
+ters!
  JOIN stsc.sku ON sku.loc = h.loc and sku.ITEM = h.dmdunit
 WHERE a.loc     = ?
   AND a.dmdunit = ?
 GROUP BY a.loc, a.dmdunit, b.p_dlrnet, b.p_dlrsplrcode
[download]

No need for dynamic SQL. So you are more likely to be able to reuse the query plan instead of forcing Oracle to recompile and reoptimize your query every time.

{REM}I think I'll go crazy when I finally move to (name snipped to protect the quilty) with their 8500 tables in the database. A separate table for the usernames&passwords of each client ... oh my! And the butt that deserves to be kicked is long gone.{/REM}

Jenda


Support Denmark! Defend the free world!

Can anyone please help me with this?