Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Message-ID: <20030303123315.27581.qmail@web41401.mail.yahoo.com>
Date: Mon, 3 Mar 2003 04:33:15 -0800 (PST)
From: Jeffrey Gehrung <jeffgehrung@yahoo.com>
Subject: Re: AW: Guru Challenge: Correlated Subquery
To: Turbine Torque Users List <torque-user@db.apache.org>
In-Reply-To: <001201c2df24$3dddd4c0$3964a8c0@buddyxp>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Thanks to all for your advice, and, in particular, J�rgen.

Seems I was standing too close to the problem...  I chose to use your
advice and make do with BasePeer.executeQuery("String sql").  This
should have been my initial choice for two reasons:
1) the complexity of the SQL statement
2) I'm providing the app to a client with little JAVA expertise.  The
best fit for them is to place the raw SQL string in an external
Properties file (in Prepared Statement format) so they can add/revise
queries as need be without having to revise the JAVA Code and the need
to learn "Torqu-ese".  At run time,  I retrieve the corresponding
property and execute the SQL from within a PreparedStatement.  

Again... Thanks.

Jeff Gehrung


--- J�rgen_Hoffmann <jh@byteaction.de> wrote:
> Hi,
> 
> Remember that you can always use BasePeer.executeQuery("String sql")
> which returns you a list of Village Records, That you can then use in
> your Program. I most often find this easier to use with complex
> queries.
> 
> Kind regards
>  
> J�rgen Hoffmann
> ByteACTION GmbH
>  
> cert. Perl Programmer
> cert. Linux System Administrator
> cert. Java Programmer
> 
> Besuchen Sie uns doch auf der CeBIT 2003
> in Halle 4/Stand 70
> 
> 
> -----Urspr�ngliche Nachricht-----
> Von: Jeffrey Gehrung [mailto:jeffgehrung@yahoo.com] 
> Gesendet: Freitag, 28. Februar 2003 13:10
> An: torque-user@db.apache.org
> Betreff: Guru Challenge: Correlated Subquery
> 
> 
> For the Torque guru:
> 
> I've spent spent two days trying to convert this into "torque-ese"
> but
> can't seem to link in the final SELECT....  Now trying it using
> Criteria.CUSTOM and Criterion's.
> 
> The cruxt:
> For a collection of legitimate animals, an animal may appear multiple
> times differentiated by owner count...  I only need animals in this
> collection if they appear with the MAX owner count (indicating the
> record pertains to the most recent owner) - the following SELECT has
> been tested to work properly:
> 
> Note: the "a" table in the final inner SELECT must be linked to the
> "a"
> table in the outer SELECT for this statement to work properly:
> 
> SELECT a.acct_nbr, a.anm_key, a.ownr_cnt, a.ownr_dt
>   FROM anm_acct a   anm_traits t 
>   WHERE a.acct_nbr = '123456'
>   and t.the_stat_cd < 7     
>   and a.anm_key = (
>       SELECT b.anm_key
>         FROM anm_acct b
>         WHERE a.anm_key = b.anm_key
>         and a.ownr_cnt = b.ownr_cnt
>         and a.ownr_cnt = (
>             SELECT max(b.ownr_cnt)
>               FROM anm_acct b 
>               WHERE a.anm_key = b.anm_key
>             )
>       )
> 
> TIA !!!
> 
> =====
> 
> -------------------------------------- 
> Jeff Gehrung (Sr. Programmer Analyst) 
> Holstein Association USA 
> Brattleboro VT 05302 
> 802.451.4146 
> jeffgehrung@yahoo.com 
> jgehrung@holstein.com 
> 
> 
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Tax Center - forms, calculators, tips, more
> http://taxes.yahoo.com/
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 


__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/