db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jürgen Hoffmann ...@byteaction.de>
Subject AW: Guru Challenge: Correlated Subquery
Date Fri, 28 Feb 2003 12:23:46 GMT

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

Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more

To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

View raw message