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:[EMAIL PROTECTED]
Gesendet: Freitag, 28. Februar 2003 13:10
An: [EMAIL PROTECTED]
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
[EMAIL PROTECTED]
[EMAIL PROTECTED]
__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
