I think I forget a constraint...
Richard Bayet a écrit :
Try this one :Here :
/* 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);
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:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
--------------------------------------------------------------------- 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]
