Return-Path: <phillip.barnes@gmail.com>
Mailing-List: contact ibatis-user-java-help@incubator.apache.org; run by ezmlm
Delivered-To: mailing list ibatis-user-java@incubator.apache.org
Received: (qmail 306 invoked by uid 99); 18 Feb 2005 16:46:25 -0000
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=RCVD_BY_IP,SPF_HELO_PASS,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (hermes.apache.org: domain of phillip.barnes@gmail.com
 designates 64.233.184.192 as permitted sender)
Received: from wproxy.gmail.com (HELO wproxy.gmail.com) (64.233.184.192)
  by apache.org (qpsmtpd/0.28) with ESMTP; Fri, 18 Feb 2005 08:46:25 -0800
Received: by wproxy.gmail.com with SMTP id 69so475329wra
        for <ibatis-user-java@incubator.apache.org>;
 Fri, 18 Feb 2005 08:46:23 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references;
        b=kB8OZ89QD97XLoRQ3JeJV5ZthM+nCat6/AORq0n+cs5kCd4Oos0ryOm8F2B1fDlqhEq5zTv1Ld33Y72JfC/YvTfhX8vua7RaUF9/oyYPl7fPqBomACsl8+P1ZHGvP8cOq3vhMNE0jQcYpWGCdkbDUbEwPAiEJ1sDnAuDV+rxDFA=
Received: by 10.54.30.28 with SMTP id d28mr97510wrd;
        Fri, 18 Feb 2005 08:46:21 -0800 (PST)
Received: by 10.54.24.33 with HTTP; Fri, 18 Feb 2005 08:46:20 -0800 (PST)
Message-ID: <8fcb3af005021808462da22505@mail.gmail.com>
Date: Fri, 18 Feb 2005 11:46:20 -0500
From: Phil Barnes <phillip.barnes@gmail.com>
Reply-To: Phil Barnes <phillip.barnes@gmail.com>
To: ibatis-user-java@incubator.apache.org
Subject: Re: Passing parameters to sub selects
In-Reply-To: <2fe5ef5b050218081834f08d87@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
References: <8fcb3af00502180727773955fc@mail.gmail.com>
	 <2fe5ef5b050218081834f08d87@mail.gmail.com>
X-Virus-Checked: Checked

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..
> >
>