Function « SQL « JPA Q&A





1. Is it possible to use analytic functions in Hibernate?    stackoverflow.com

Is there a way to use sql-server like analytic functions in Hibernate? Something like

select foo from Foo foo where f.x = max(f.x) over (partition by f.y)

2. HQL concat function    stackoverflow.com

Customer{
  String customerName
  @OneToMany
  Set users;
}
User{
  String userName;
}
when i do this: select c.customerName as customerName ,concat(u.userName) as userNames from Customer c join c.users as u hibernate don't return ...

3. SQL Functions in Hibernate    coderanch.com

Hi, Is there a way for me to replicate the built-in DB2 functions when using Hibernate? I need to trim the left and right spaces, and then convert the field in where-clause to upper case for comparison purposes. Cut down to the simplest form, my query presently looks like: Session session = getCurrentSession(); Query countOpCodeQry = session.createQuery( " select count(*) from ...

4. Hibernate native SQL support for user defined functions    forum.hibernate.org

Hi All, I am using Hibernate 3.6.5, and using native sql to call a query. I am using the method session.createSQLQuery(finalquery); My query has user defined functions in the query, something like "Select GETSUMMARYVIEWPARTIES(aoi.AOITARGETPFI),aoi.NAME from AOI aoi" When I run this query in SQL client(squirrel) I get the required results but when I run it using hibernate session I get the ...

5. MONTH() SQL function support    forum.hibernate.org

6. help with native sql function    forum.hibernate.org

hi, we are using oracle as the backend. I am migrating from hand written sql code to hibernate. we have a function called encrypt_password. and we have a sql like this: select firstName, lastName, encrypt_password(password) from user does hibernate support sql like that? I tried several ways, but looks like I have no way to map 'encrypt_password(password)' to a java attribute. ...

7. Dynamic instatiation with native sql function    forum.hibernate.org

8. How to use substring sql function ?    forum.hibernate.org

Hi, I'm trying to use substring in a hql query. And I always get an error/exception. Looking in this forum, I found 2 topics but it stille doesn't work. I would like to execute the following query : select distinct substring(cbb.nom, 1, 1) as letter from CyberBase as cbb order by cbb.nom asc Could you help me to do it ? ...





10. SQL Function "passthrough" in HQL after migration    forum.hibernate.org

Thanks for the reply. I have created a custom dialect which extends the Dialect I'm using. It registers the NoArgSQLFunction, specifiying the name of the function. For a test, I wanted to see if the HQL parser would "ignore" the test SQL function "my_month_func"; however, it is still trying to parse it. I'd expect it to fail on the database end. ...

11. ClasscQueryTranslator with SQL functions    forum.hibernate.org

Hello I have faced with the following problem. I use Hibernate-3.1.3 with ClassicQueryTranslatorFactory under MySQL 4.0.20d. I need the SQL function "day". MySQL 4.0 does not have day function, but it has equal "dayofmonth". MySQLDialect.java has the following definition: Code: registerFunction("day", new StandardSQLFunction("day", Hibernate.INTEGER) ); In order to have compatibility both with MySQL ...

12. HQL with multiple sql functions?    forum.hibernate.org

Hi, I have a newbie question about HQL. ... ... - I'm terribly sorry if I'm wasting your time by missing something totally obvious! Right. I found the answer in "Java Persistence with Hibernate" (14.3.3). Evidently subselects are only supported in the WHERE clause... Sorry to bother you all! I'll stick with the session.createSQLQuery() solution. No, hey! got it!: MyMappedClass anObject ...

13. Call SQL functions by using registerFunction    forum.hibernate.org

TABLE_A.FIELD_A as H110_1_, from TABLE_A where TABLE_A.FIELD_B = XYZ into char(TABLE_A.FIELD_A) as H110_1_, from TABLE_A where ...

14. SQL Replace function in HQL    forum.hibernate.org

15. How to declare an index with an sql function    forum.hibernate.org

16. Error while using the DATEADD function in Hibernate+SQL    forum.hibernate.org

I am getting the errors while executing DATEADD function in hibernate. My database is Microsoft SQL server. EX: Query q1 = this.session .createQuery("select DATEADD(Month, -3, 3-1-2008)"); Error: Caused by: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode \-[METHOD_CALL] MethodNode: '(' +-[METHOD_NAME] IdentNode: 'DATEADD'{originalText=DATEADD} \-[EXPR_LIST] SqlNode: 'exprList' +-[IDENT] IdentNode: 'Month' {originalText=Month} +-[UNARY_MINUS] UnaryArithmeticNode: '-' | \-[NUM_INT] LiteralNode: '3' \-[MINUS] BinaryArithmeticOperatorNode: '-' {dataType=org.hibernate.type.IntegerType@b49448} +-[MINUS] ...





17. Create function in SQL    forum.hibernate.org

18. datepart function for sql 2005    forum.hibernate.org

select custreques0_.CUST_REQ_ID_NBR as CUST1_160_0_, opptys1_.OPTY_ID_NBR as OPTY1_162_1_, custreques0_.CORP_ENT_CD as CORP2_160_0_, custreques0_.CUST_NBR as CUST3_160_0_, custreques0_.BSTAR_ACCT_NBR as BSTAR4_160_0_, custreques0_.CUST_NM as CUST5_160_0_, custreques0_.RPTG_CUST_NM as RPTG6_160_0_, custreques0_.UNDRWRTG_SEG_CD as UNDRWRTG7_160_0_, custreques0_.SLS_REPV_NM as SLS8_160_0_, custreques0_.ACCT_EXCUTV_NM as ACCT9_160_0_, custreques0_.PRPSL_CRTE_DT as PRPSL10_160_0_, custreques0_.PRPSL_NBR as PRPSL11_160_0_, custreques0_.LST_MDFY_BY as LST12_160_0_, custreques0_.CRTE_BY as CRTE13_160_0_, custreques0_.LST_MDFY_DT as LST14_160_0_, custreques0_.CRTE_DT as CRTE15_160_0_, opptys1_.RTG_EVNT_ID_NBR as RTG2_162_1_, opptys1_.OPTY_DESC as OPTY3_162_1_, opptys1_.OPTY_DETL_DESC as OPTY4_162_1_, opptys1_.COVRG_CD as COVRG5_162_1_, ...