Re: AW: Antwort: SQL->Criteria howto

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);

/* 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@xxxxxxxxxx]
> 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@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx
> > > For additional commands, e-mail: torque-user-help@xxxxxxxxxxxxx
> >
> >
> >
> > ---------------------------------------------------------------------