Re: AW: Antwort: SQL->Criteria howto

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@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
> > >
> > >
> > >
> > > ---------------------------------------------------------------------
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@xxxxxxxxxxxxx
> For additional commands, e-mail: torque-user-help@xxxxxxxxxxxxx