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

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 13 posts ] 
Author Message
 Post subject: HQ oracle SUBSTR
PostPosted: Tue Apr 20, 2004 3:48 am 
Newbie

Joined: Fri Mar 12, 2004 11:04 am
Posts: 10
Hibernate 2.1.2

Hi,

How can I write this ORACLE query in HQ ?:


select count(d.ED_ID), SUBSTR(d.ED_VALUE_STRING,1,3) kvc
from epd_details d,
epd p, columns c, keylist_values kv
where
(d.ED_EPD_ID = p.EPD_ID) and
(d.ED_COL_ID = c.COL_ID) and
(d.ED_KLD_ID = kv.KLD_ID) and
(kv.KLD_KL_ID = 15 )
group by SUBSTR(d.ED_VALUE_STRING,1,3);

Any help appreciated. Thx.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Apr 20, 2004 4:04 am 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
Not possible yet, use direct JDBC


Top
 Profile  
 
 Post subject:
PostPosted: Tue Apr 20, 2004 4:06 am 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
is it possible in a formula?


Top
 Profile  
 
 Post subject:
PostPosted: Tue Apr 20, 2004 4:14 am 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
Hm, might be worth a try ...


Top
 Profile  
 
 Post subject: Re: HQ oracle SUBSTR
PostPosted: Fri Feb 11, 2005 10:50 am 
Newbie

Joined: Fri Feb 11, 2005 5:36 am
Posts: 5
dzafer wrote:
Hibernate 2.1.2

Hi,

How can I write this ORACLE query in HQ ?:


select count(d.ED_ID), SUBSTR(d.ED_VALUE_STRING,1,3) kvc
from epd_details d,
epd p, columns c, keylist_values kv
where
(d.ED_EPD_ID = p.EPD_ID) and
(d.ED_COL_ID = c.COL_ID) and
(d.ED_KLD_ID = kv.KLD_ID) and
(kv.KLD_KL_ID = 15 )
group by SUBSTR(d.ED_VALUE_STRING,1,3);

Any help appreciated. Thx.


Can someone tell me if it's possible Hibernate 3?
Thanks.
Mara


Top
 Profile  
 
 Post subject:
PostPosted: Fri Feb 11, 2005 11:26 am 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
I try next with hibernate3 beta4 and caveatemptor
Code:
select count(bid.id),substr(bid.name,1,3)
from Bid bid
group by substr(bid.name,1,3)


it isn't work - error is line 3:16: unexpected token: (


regards


Top
 Profile  
 
 Post subject:
PostPosted: Fri Feb 11, 2005 12:27 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
i play with this query
Code:
select count(bid.id),substr(bid.item.name,1,3)
from Bid bid
group by substr(bid.item.name,1,3)

and last hibernate 3

when I change line 275 in grammar/hql.g
( "by"! path ( COMMA! path )* )? // NOTE: The '!' after "by" tells ANTLR to skip AST generation for the token.

with

( "by"! expression ( COMMA! expression )* )? // NOTE: The '!' after "by" tells ANTLR to skip AST generation for the token.

it work

Joshua, is it fine ?

regards


Top
 Profile  
 
 Post subject: Re: HQ oracle SUBSTR
PostPosted: Fri Feb 11, 2005 12:33 pm 
Beginner
Beginner

Joined: Tue Nov 02, 2004 1:34 pm
Posts: 45
Not a solution, but a work around....create a query in oracle that has the substr in it....then use hibernate to manipulate the query.

Lee


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 16, 2005 8:51 am 
Newbie

Joined: Fri Feb 11, 2005 5:36 am
Posts: 5
michael wrote:
Not possible yet, use direct JDBC

Hi,
I'm using Oracle 8 +hibernate 3+Tomcat 4.1
and this query work!

select substr(c.Date,6,2), avg(c.Value)from Table c
where substr(c.Date,1,4)='2003'
group by substr(c.Date,6,2)

Why do you say to us to use jdbc?

But I've notice a stange thing: using MySQL (and mysql dialect) this query doesn't work!
I recive an error message like this:"Alias undefined on query ..."
(Query syntax in mysql is correct, using jdbc it works!)
Can someone tell me the reason of this difference using Oracle and using MySQL?

Thanks.
Mara


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 16, 2005 12:55 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
h3 work with old parser, but new parser (from Josua) don't work

regards


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 16, 2005 7:03 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia
marace wrote:
But I've notice a stange thing: using MySQL (and mysql dialect) this query doesn't work!
I recive an error message like this:"Alias undefined on query ..."
(Query syntax in mysql is correct, using jdbc it works!)
Can someone tell me the reason of this difference using Oracle and using MySQL?

Thanks.
Mara


Each database dialect has a number of standard and non-standard select functions. Oracle dialect has a very large number of them, I assume from the error (I have not looked) that MySQL does not have that function defined in the dlalect. See Oracle dialect source and add it to the MySQL dialect - if it works submit a patch.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Feb 18, 2005 5:36 am 
Newbie

Joined: Fri Feb 11, 2005 5:36 am
Posts: 5
snpesnpe wrote:
h3 work with old parser, but new parser (from Josua) don't work

regards

Why the new parser version doesn't support this feature?
Is it a bug or there is a reason for this?
If this feature wont be in future Hibernate releases, maybe it's better if I don't use it in my code, or I can choose to use this feature without to change the Hibernate version that I'm using?
What advised to me?
Thanks.
Mara


Top
 Profile  
 
 Post subject:
PostPosted: Fri Feb 18, 2005 8:27 am 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
I think that is bug.I send patch/workoaround before in this thread, but I don't know if it break another thing

regards


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 13 posts ] 

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.