in reply to Re: Help with Dynamic SQL subroutine
in thread Help with Dynamic SQL subroutine

I'm using Oracle 9.2.0.2. I don't think it's a problem with the query because I can cut and paste it into TOAD and it runs with no problem. This is what's getting prepared: 
"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]

Replies are listed 'Best First'.
Re^3: Help with Dynamic SQL subroutine
by runrig (Abbot) on Nov 27, 2006 at 20:54 UTC
    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.
[reply]
Re^3: Help with Dynamic SQL subroutine
by Jenda (Abbot) on Nov 27, 2006 at 23:41 UTC

    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!

[reply]
[d/l]
Re^3: Help with Dynamic SQL subroutine
by sasrs99 (Acolyte) on Nov 27, 2006 at 19:33 UTC
    Can anyone please help me with this?
[reply]

In Section Seekers of Perl Wisdom