You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by "Sperlich, Tino" <t....@hpc-hamburg.de> on 2004/11/22 11:40:18 UTC

SQL->Criteria howto

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, VORAVIS va", 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

Re: Antwort: SQL->Criteria howto

Posted by Richard Bayet <ba...@enseirb.fr>.
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....@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

Antwort: SQL->Criteria howto

Posted by Thomas Fischer <Fi...@seitenbau.net>.



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....@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