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 Passing parameters to sub selects
Date Fri, 18 Feb 2005 15:27:15 GMT
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