Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Users List" <torque-user@db.apache.org>
Received-SPF: neutral (hermes.apache.org: local policy)
Message-ID: <41A1D7A1.6070502@enseirb.fr>
Date: Mon, 22 Nov 2004 13:12:17 +0100
From: Richard Bayet <bayet@enseirb.fr>
User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913)
MIME-Version: 1.0
To: Apache Torque Users List <torque-user@db.apache.org>
Subject: Re: AW: Antwort: SQL->Criteria howto
References: <96F363742E3BAF4EBE5EA6A846DA14B417EF63@mail.hpc-hh.de>
 <41A1D6F4.3010507@enseirb.fr>
In-Reply-To: <41A1D6F4.3010507@enseirb.fr>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Received-SPF: none (bxnms.u-bordeaux.fr: 147.210.157.56 is neither permitted
 nor denied by domain of enseirb.fr) receiver=bxnms.u-bordeaux.fr;
 client-ip=147.210.157.56; helo=[147.210.157.56];
 envelope-from=bayet@enseirb.fr;
 x-software=spfmilter 0.93 http://www.acme.com/software/spfmilter/;

I think I forget a constraint...

Richard Bayet a �crit :
> Try this one :
> /* 1) the subSelect */
> Criteria subSelectCrit = new Criteria();
> subSelectCrit.addAlias("T2", VoravisPeer.TABLE_NAME); // (or simply 
> "VORAVIS")
> subSelectCrit.addSelectColumn("MAX(T2.SEND_SEQUENZ)");
> subSelectCrit.add("T2.AUFT_ID", (Object)"T2.AUFT_ID = T1.AUFT_ID", 
> Criteria.Custom);
Here :
subSelectCrit.add("T2.SEQUENZ", (Object)"T2.SEQUENZ = T1.SEQUENZ", 
Criteria.Custom);

> 
> /* 2) the "top" part of the request */
> Criteria topPartCrit = new EnhancedCriteria();
> topPartCrit.addAlias("T1", VoravisPeer.TABLE_NAME);
> /*
> Might be necessary if you want to restrict to the columns "vavi_id" & 
> "sequenz"
> topPartCrit.addSelectColumn("T1.VAVI_ID");
> topPartCrit.addSelectColumn("T1.SEQUENZ")
> */
> /* addIn : see (3) */
> topPartCrit.addIn("T1.SEND_SEQUENZ", subSelectCrit);
> topPartCrit.add("T1.AUFT_ID", new Integer(#your_value#)));
> 
> 3) Requirement :
> A method "Criteria.addIn(String column, String Criteria)", but obviously 
> you already have it.
> 
> There might be some flaws concerning the number of columns you want to 
> get (all or only vavi_id & sequenz), and the fact that you might want 
> the Peer Objects associated with the results (that is using 
> VoravisPeer.doSelect(topPartCrit)) or just the records 
> (VoravisPeer.doSelectVillageRecords(topParCrit)).
> But the subquery problem should be ok.
> 
> Regards.
> 
> Sperlich, Tino a �crit :
> 
>> Hi all,
>>
>> it may be a bit confusing, there are two
>> sequences SEND_SEQUENZ and just SEQUENZ.
>>
>> Indeed, I need to have the max(SEND_SEQUENZ)
>> for every distinct SEQUENZ I find, hence the
>> IN clause.
>> Here is a data example:
>>
>> VAVI_ID    AUFT~ID    SEQUENZ    SEND~SEQUENZ
>> 88139        67537        2        1
>> 91329        67537        2        2
>> 91330        67537     1        1
>>
>> only this should be selected: max(send_sequenz) from every sequenz
>>
>> VAVI_ID    AUFT_ID    SEQUENZ    SEND_SEQUENZ
>> 91329        67537        2        2
>> 91330        67537        1        1
>>
>> any hints how to "carve" that into a criteria?
>> Thanks again,
>> Tino
>>
>> -----Urspr�ngliche Nachricht-----
>> Von: Richard Bayet [mailto:bayet@enseirb.fr]
>> Gesendet: Montag, 22. November 2004 12:30
>> An: Apache Torque Users List
>> Betreff: Re: Antwort: SQL->Criteria howto
>>
>>
>> Hi all,
>>
>> Thomas' answer may not be very accurate (it's simpler than Tino what 
>> Tino's need), but "select vavi_id, max(sequenz) from voravis
>> where vavi_id = 67537" should be fine.
>>
>> Of course, if you (Tino) want the max(sequenz) for every distinct 
>> vavi_id, you'll need the IN clause.
>>
>> Thomas Fischer a �crit :
>>
>>>
>>>
>>> Hi Tino
>>>
>>> 1) I do not understand why you need the alias. Following sql works 
>>> for me
>>> (on tutorial tables, on oracle)
>>> select * from book where book_id in (select max(book_id) from book);
>>>
>>>  Thomas
>>>
>>> "Sperlich, Tino" <t.sperlich@hpc-hamburg.de> schrieb am 22.11.2004
>>> 11:40:18:
>>>
>>>
>>>
>>>> Hi all,
>>>>
>>>> I'd like to express this SQL in criteria syntax, but smth. is still
>>>
>>>
>>> missing:
>>>
>>>
>>>> SELECT VORAVIS.VAVI_ID, VORAVIS.SEQUENZ
>>>
>>>
>>>> FROM VORAVIS
>>>
>>>
>>>> WHERE VORAVIS.AUFT_ID=67537 AND
>>>> VORAVIS.SEND_SEQUENZ
>>>> IN
>>>> (
>>>> SELECT MAX(va.SEND_SEQUENZ)
>>>> FROM VORAVIS va
>>>> WHERE va.AUFT_ID=VORAVIS.AUFT_ID AND va.SEQUENZ=VORAVIS.SEQUENZ
>>>> )
>>>>
>>>> My main problem is how to create the table alias "va" in the IN clause.
>>>> Using the criteria.addAlias() method gives me "FROM VORAVIS, 
>>>> VORAVISva",
>>>
>>>
>>> i.e.
>>>
>>>
>>>> double table statements.
>>>> For the in clause I use an adjusted criteria class supporting the
>>>> addIn(column, criteria) operation, basically just writing "column IN
>>>
>>>
>>> query".
>>>
>>>
>>>> What am I missing?
>>>>
>>>> Thanks,
>>>> Tino
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: torque-user-help@db.apache.org
>>>>
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 


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