db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeffrey Gehrung <jeffgehr...@yahoo.com>
Subject Guru Challenge: Correlated Subquery
Date Fri, 28 Feb 2003 12:09:55 GMT
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/

Mime
View raw message