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.  [ 1 post ] 
Author Message
 Post subject: HQL: MySQL Conditional logic issues
PostPosted: Mon Dec 12, 2005 8:03 am 

Joined: Mon Dec 12, 2005 7:40 am
Posts: 1
Hibernate version: 3

select f
  from ErrorFact f
  left join fetch f.time time
  where 1=1
  and if ( time.year = :Integer2
    , if ( time.month = :Integer3
      , if ( (time.day is null) or (time.day = :Integer4)
        , (time.hour is null) or (time.hour <= :Integer5)
        , time.day < :Integer4 )
      , time.month < :Integer3 )
    , time.year < :Integer6 )

Full stack trace of any exception that occurs:
could not execute query

Name and version of the database you are using:
MySQL v. 4.0.23

The generated SQL (show_sql=true):
select errorfact0_.OID as OID0_
, errortimed1_.TIME_OID as TIME1_1_
, errorfact0_.TIME_OID as TIME2_7_0_
, errortimed1_.YEAR as YEAR8_1_
, errortimed1_.MONTH as MONTH8_1_
, errortimed1_.DAY as DAY8_1_
, errortimed1_.HOUR as HOUR8_1_
, errortimed1_.MINUTE as MINUTE8_1_
from LP_STAR_ERROR_FACT errorfact0_
left outer join LP_STAR_TIME_DIM errortimed1_ on errorfact0_.TIME_OID=errortimed1_.TIME_OID
where (1=1 )
and( if(errortimed1_.YEAR=2005
  , if(errortimed1_.MONTH=4
    ,  if((errortimed1_.DAY is null ))or((errortimed1_.DAY)=1
      , (errortimed1_.HOUR is null ))or((errortimed1_.HOUR)<=0
      , errortimed1_.DAY<1 )
    , errortimed1_.MONTH<4 )
  , errortimed1_.YEAR<2005 )

I checked online through several Google searches and a few search's on this site. Unfortunately I was not able to find a answer to my problem.

The problem being that the HQL -> SQL translation doesnt appear to see IF statements as If statements, rather it sees it as a paren block. The If statements work as long as you only have one conditional statement in each spot. As soon as you have two it attempts to group them in parens and ends up breaking the If statement so that MySQL cannot parse the If statement properly.

I am suspecting that I would be better off using dynamic SQL for this rather than dynamic HQL, I have just dedicated so much time to this approach already that I am hesitant to give it up now *grin*

Any help would be appreciated tremendously.



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

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.