db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Quinton McCombs" <qmcco...@nequalsone.com>
Subject RE: Guru Challenge: Correlated Subquery
Date Fri, 28 Feb 2003 16:16:20 GMT
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:jeffgehrung@yahoo.com] 
> Sent: Friday, February 28, 2003 6:10 AM
> To: torque-user@db.apache.org
> 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 
> 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


Mime
View raw message