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}

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.