ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Passing parameters to sub selects
Date Fri, 18 Feb 2005 16:18:52 GMT
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