Hibernate version: 3
HQL:
Code:
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
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:370)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
Name and version of the database you are using:MySQL v. 4.0.23
The generated SQL (show_sql=true):Code:
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.
Cheers,
Jonmark
Login
FAQ
Search