Thanks for the reply Brandon. That was indeed one of the "SQL-related
options" I referred in my original message.
There are a few reasons I didn't automatically do this: the first is
that I did not write the original SQL, it was "handed off" to me, and
the second is that I'm completely unfamiliar with the underlying data
model, and third, I am under an (unreasonably) agressive timeline --
obviously a difficult situation to be in as a developer. ;)
Your second suggestion is another approach I had done in a different
report, in which a JOIN would have been ideal (as the results from one
select were used as parameters in the second select, etc.) -- reasons
for not turning this into a JOIN, see the previous paragraph. ;)
I suppose I was just hoping that there was something simple I was
missing -- like specifying inline parameters in a results map or
something.
Either way, I have a working solution, and 2 additional suggestions if
it becomes a problem. :) Thanks.
On Fri, 18 Feb 2005 09:18:52 -0700, Brandon Goodin
<[EMAIL PROTECTED]> wrote:
> I'm curious why you don't simply write this as an actual subselect
> since it passes back a single value java type.
>
> Also, result selects are a convenience feature for simple to
> moderately complex scenarios. Don't be afraid to implement an iterator
> in your dao class that iterates over the getResults results and calls
> the getShippedTotal with the proper parameters for each item. You can
> pass the object in your list as a parameter and populate a property on
> it that is needed.
>
> Brandon
>
>
> On Fri, 18 Feb 2005 10:27:15 -0500, Phil Barnes
> <[EMAIL PROTECTED]> wrote:
> > Hi all,
> >
> > First, I'd like to say "thanks" for all the work on iBATIS SqlMaps
> > (and the DAO framework which I haven't tried -- yet!). I'm currently
> > working on a set of "web reports", and iBATIS has made a terrific time
> > saver.
> >
> > My question is about how to pass paramters from a "parameterClass" to
> > a subselect via a <result ... select=".."/> or if it's possible.
> > Right now, I'm mapping the value directly to the SQL statement
> > "dynamically", and passing that "aliased" column on to the subquery.
> > However, this seems rather "clunky", especially for Date objects.
> >
> > An "working" example (reduced for brevity -- hopefully it still makes
> > sense):
> >
> > <resultMap id="daily-sales" class="test.report.DailySalesResult">
> > <result property="customerNumber" column="customer_number"/>
> > <result property="shippedTotal"
> > column="{customerId=customer_id,date=run_date}"
> > select="DailySalesReport.getShippedTotal"/>
> > </resultMap>
> >
> > <select id="getResults" resultMap="daily-sales"
> > parameterClass="test.report.DailySalesReport">
> > SELECT ca.account_number customer_number
> > ,brl.cust_account_id customer_id
> > ,SUM (brc.VALUE) forecast
> > ,#dateString# run_date
> > FROM budget_report_lookups brl
> > ,budget_report_cells brc
> > ,cust_accounts ca
> > WHERE brc.budget_cell_id = brl.budget_cell_id
> > AND brl.lookup_code NOT IN ('COS', 'DIR', 'FR')
> > AND brc.MONTH = TO_CHAR (#dateDB#, 'MON')
> > AND brc.YEAR = TO_CHAR (#dateDB#, 'YYYY')
> > AND ca.cust_account_id = brl.cust_account_id
> > GROUP BY ca.account_number
> > ,brl.cust_account_id
> > ,brc.YEAR
> > ,brc.MONTH
> > </select>
> >
> > <select id="getShippedTotal" resultClass="double">
> > SELECT SUM (NVL (ool.unit_selling_price, 0) * NVL
> > (ool.shipped_quantity, 0)) ship_total
> > FROM order_lines ool
> > WHERE TRUNC (ool.actual_shipment_date, 'MM') = TRUNC
> > (TO_DATE(#date#), 'MM')
> > AND ool.sold_to_org_id = #customerId#
> > GROUP BY ool.sold_to_org_id
> > </select>
> >
> > As you can see, #dateString# is a string representation, which ends up
> > looking something like '01-FEB-2005', whereas #dateDB# is the actual
> > "Date object".
> >
> > Ideally, I'd like to write someting like:
> > <result property="shippedTotal"
> > column="{customerId=customer_id,date=#dateDB#}"
> > select="DailySalesReport.getShippedTotal"/>
> >
> > where the #dateDB# field is pulled from the parameterClass and passed
> > to the "subselect".
> >
> > I can think of a number of SQL-related solutions, but I was hoping
> > there was something I can't seem to find in either the Dev Guide or on
> > the Mailing List Archives.
> >
> > Thanks in advance,
> >
> > Phil..
> >
>
