Hmmm.... It will be interesting to see if you can get Torque to generate the correct code for this. You might have to use Criteria.CUSTOM and pass in the " a.anm_key = ( ... )".
Don't get me wrong, there might be a way to do it using the Criterions... Using Criteria.CUSTOM as I just mentioned might be a fall back solution for you if you can't get it working the "correct" way. > -----Original Message----- > From: Jeffrey Gehrung [mailto:[EMAIL PROTECTED] > Sent: Friday, February 28, 2003 6:10 AM > To: [EMAIL PROTECTED] > Subject: 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]
