ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Phil Barnes <phillip.bar...@gmail.com>
Subject Re: Passing parameters to sub selects
Date Fri, 18 Feb 2005 16:46:20 GMT
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
<brandon.goodin@gmail.com> 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
> <phillip.barnes@gmail.com> 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..
> >
>

Mime
View raw message