-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
gcorro
 Post subject: Translating Oracle's (+) to hibernate
PostPosted: Thu Aug 23, 2007 5:35 am 
Newbie

Joined: Fri Aug 17, 2007 5:45 am
Posts: 6
Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 1.3.1

Name and version of the database you are using: Oracle 10i


Hi,

I have the next query in Oracle:

select *
from Resend R, AuditsOnline AO, AuditsBatch AB
where
R.timestamp >= to_date( '21/8/07' ,'dd/mm/yy HH24:MI')
and R.timestamp <= to_date( '22/8/07' ,'dd/mm/yy HH24:MI')
and R.msgid=AO.msgId(+)
and R.fichero=AB.fichero(+)
group by R.orden order by max(R.timestamp) desc


How can a translate the '(+)' symbol to hibernate.

thank you!
Top
 Profile  
 
gcorro
 Post subject: Outer join change from oracle to hibernate
PostPosted: Thu Aug 23, 2007 9:07 am 
Newbie

Joined: Fri Aug 17, 2007 5:45 am
Posts: 6
To be more precise, the original Oracle-SQL query looks like:

Code:
select R.orden as Orden, Max(R.fichero) as Fichero, 
Max(R.msgid) as MsgId, max(R.timestamp) as timestamp, 
Max(R.idusuario) as IdUsuario,  Max(R.msgidreenviado) as MsgIdReenviado, 
Max(AO.orden) as ordenAudO, Max(AO.codigoexplotacion) as CodigoExplotacionAudO, 
Max(AO.valorsignifictrans) as ValorSignificTransAudO, Max(AO.estado) as EstadoAudO, 
Max(AO.tipoapunte) as TipoApunteAudO, Max(AO.timestamp) as TimeStampAudO, 
Max(AO.msgId) as MsgIdAudO, Max(AO.codigointerfaz) as CodigoInterfazAudO, 
Max(AB.orden) as ordenAudB, Max(AB.codigoexplotacion) as CodigoExplotacionAudB, 
Max(AB.fecha) as FechaAudB, Max(AB.codigoInterfaz) as CodigoInterfazAudB, 
Max(AB.programa) as programaAudB, Max(AB.qmanager) as QManagerAudB, 
Max(AB.fichero) as FicheroAudB, Max(AB.tamanofichero) as TamanoFicheroAudB, 
Max(AB.estado) as EstadoAudB, Max(AB.almacenada) as AlmacenadaAudB, 
Max(AB.tipoapunte) as TipoApunteAudB, Max(AO.timestamp) as TimeStampAudB, 
Max(AB.caducidad) as caducidadAudB 
from Reenvios R, AuditoriasOnline AO, AuditoriasBatch AB 
where 
R.timestamp >= to_date( '21/8/07' ,'dd/mm/yy HH24:MI') 
and  R.timestamp <= to_date( '22/8/07' ,'dd/mm/yy HH24:MI') 
and R.msgid=AO.msgId(+)
and R.fichero=AB.fichero(+) 
group by R.orden  order by max(R.timestamp) Desc



And I'm trying to migrate to hibernate this way:



Code:
select R.orden, max(R.fichero),  max(R.msgid), max(R.timestamp), 
max(R.idusuario),  max(R.msgidreenviado),  max(AO.orden),
max(AO.codigoexplotacion),  max(AO.valorsignifictrans), max(AO.estado), 
max(AO.tipoapunte), max(AO.timestamp),  max(AO.msgid),
max(AO.codigointerfaz),  max(AB.orden), max(AB.codigoexplotacion), 
max(AB.fecha), max(AB.codigointerfaz),  max(AB.programa),
max(AB.qmanager),  max(AB.fichero), max(AB.tamanofichero), 
max(AB.estado), max(AB.almacenada),  max(AB.tipoapunte),
max(AO.timestamp),  max(AB.caducidad) 
from Reenvios R left join AuditoriasOnline AO with R.msgid=AO.msgid, 
Reenvios R2 left join AuditoriasBatch AB with R2.fichero=AB.fichero 
where  R.timestamp >= to_date( ? ,'dd/mm/yy HH24:MI') 
and  R.timestamp <= to_date( ? ,'dd/mm/yy HH24:MI') 
and R.timestamp=R2.timestamp 
group by R.orden 
order by max(R.timestamp) desc



However it conplains about the 'from' part of the query, please help!!!
Top
 Profile  
 
hoeft
 Post subject:
PostPosted: Fri Aug 24, 2007 3:22 am 
Beginner
Beginner

Joined: Thu Apr 12, 2007 3:26 am
Posts: 35
Location: Germany
Try this one:
Code:
select ...
from Reenvios R
left join AuditoriasOnline AO with R.msgid=AO.msgid
left join AuditoriasBatch AB with R.fichero=AB.fichero
where  ...


Note: There is no "," between the join parts!

Best Regards
Hoeft
Top
 Profile  
 
hoeft
 Post subject:
PostPosted: Fri Aug 24, 2007 3:25 am 
Beginner
Beginner

Joined: Thu Apr 12, 2007 3:26 am
Posts: 35
Location: Germany
Try this one:
Code:
select ...
from Reenvios R
left join AuditoriasOnline AO with R.msgid=AO.msgid
left join AuditoriasBatch AB with R.fichero=AB.fichero
where  ...


Note: There is no "," between the join parts!

Best Regards
Hoeft
Top
 Profile  
 
gcorro
 Post subject:
PostPosted: Fri Aug 24, 2007 4:47 am 
Newbie

Joined: Fri Aug 17, 2007 5:45 am
Posts: 6
If trying:

Code:
select...
from
Reenvios R  left join AuditoriasOnline AO with R.msgid=AO.msgid 
left join AuditoriasBatch AB  with R.fichero=AB.fichero 
where ...


Hibernate resolves the query to:

Code:
...
from
com.xxx.data.hibernate.Reenvios R  left join AuditoriasOnline AO with R.msgid=AO.msgid 
left join AuditoriasBatch AB  with R.fichero=AB.fichero 
where ...


that means that both AuditoriasOnline and AuditoriasBatch are never resolved to the DTO defined in XML mapping.

Also it spits this error:
[http-8080-Processor25] ERROR - Path expected for join!
[http-8080-Processor25] ERROR - Invalid path: 'AO.orden'
[http-8080-Processor25] ERROR - Invalid path: 'AO.codigoexplotacion'
[http-8080-Processor25] ERROR - Invalid path: 'AO.valorsignifictrans'
[http-8080-Processor25] ERROR - Invalid path: 'AO.estado'
[http-8080-Processor25] ERROR - Invalid path: 'AO.tipoapunte'
[http-8080-Processor25] ERROR - Invalid path: 'AO.timestamp'
[http-8080-Processor25] ERROR - Invalid path: 'AO.msgid'
[http-8080-Processor25] ERROR - Invalid path: 'AO.codigointerfaz'
[http-8080-Processor25] ERROR - Invalid path: 'AB.orden'
[http-8080-Processor25] ERROR - Invalid path: 'AB.codigoexplotacion'
[http-8080-Processor25] ERROR - Invalid path: 'AB.fecha'
[http-8080-Processor25] ERROR - Invalid path: 'AB.codigointerfaz'
[http-8080-Processor25] ERROR - Invalid path: 'AB.programa'
[http-8080-Processor25] ERROR - Invalid path: 'AB.qmanager'
[http-8080-Processor25] ERROR - Invalid path: 'AB.fichero'
[http-8080-Processor25] ERROR - Invalid path: 'AB.tamanofichero'
[http-8080-Processor25] ERROR - Invalid path: 'AB.estado'
[http-8080-Processor25] ERROR - Invalid path: 'AB.almacenada'
[http-8080-Processor25] ERROR - Invalid path: 'AB.tipoapunte'
[http-8080-Processor25] ERROR - Invalid path: 'AO.timestamp'
[http-8080-Processor25] ERROR - Invalid path: 'AB.caducidad'
[http-8080-Processor25] ERROR - java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.AggregateNode
+-[AGGREGATE] AggregateNode: 'max'

All those errors are because those two table are not being resolved to their respective DTO

#########################

If trying:
Code:
from
AuditoriasOnline as AO, AuditoriasBatch as AB,
Reenvios as R  left join AO with R.msgid=AO.msgid  left join AB with R.fichero=AB.fichero 
where


it resolves to:

Code:
from
com.xxx.data.hibernate.AuditoriasOnline as AO,
com.xxx.data.hibernate.AuditoriasBatch as AB,
com.xxx.data.hibernate.Reenvios as R 
left join AO with R.msgid=AO.msgid  left join AB with R.fichero=AB.fichero 
where


which means it is defining the entities properly, however the error:

ERROR:org.springframework.orm.hibernate3.HibernateQueryException: Path expected for join!

is thrown.

Please assist.
Thanks in advanced.
Top
 Profile  
 
hoeft
 Post subject:
PostPosted: Fri Aug 24, 2007 5:03 am 
Beginner
Beginner

Joined: Thu Apr 12, 2007 3:26 am
Posts: 35
Location: Germany
I searched in my hibernate book (Java Persistence with hibernate, page 653):
Quote:
Note that it's currently not possible in HQL or JPA QL to outer join two tables that don't have a mapped association - theta-style joins are inner joins.


[/quote]
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.