Function « SQL « JPA Q&A

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

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

  String customerName
  Set users;
  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

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

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

6. help with native sql function

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

8. How to use substring sql function ?

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

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

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

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

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

15. How to declare an index with an sql function

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

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

18. datepart function for sql 2005

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