Hi, Phil, Can I ask you what was your work around for this problem? I
have a situation that the table name in the sub query needs to be
dynamically set.
<resultMap id="offerResult" class="offer" groupBy="id">
<result property="id" column="OfferID"/>
<result property="name" column="Name"/>
<result property="offerProductConditions" column="OfferID"
select="getProductContions"/>
</resultMap>
<select id="getOffersByFolderID" remapResults="true"
parameterClass="java.util.Map" resultMap="offerResult">
<![CDATA[
select o.OfferID,
o.Name,
from $UA_Offer$ o
where o.FolderID=#folderID#
]]>
</select>
<select id="getProductConditions" remapResults="true"
parameterClass="int" resultClass="java.lang.String">
<![CDATA[
select Condition
from $UA_OfferToProduct$
where OfferID=#offerID#
}}>
</select>
I don't know how I can pass that as a parameter to the sub select. I
know I can use the new n:m solution. But it has a bug right now with
object contains two lists that use the same groupBy id.
On Fri, 18 Feb 2005 11:46:20 -0500, Phil Barnes
<[EMAIL PROTECTED]> wrote:
> 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..
> > >
> >
>
