Subquery 2 « Query « JPA Q&A





2. Subquery help    forum.hibernate.org

I'm trying to run a select statement to retrieve records from one table based on the aggregate sum of linked records in another table, and I'm not sure how (or if) I can do this using Hibernate Criteria queries. I am modeling a domain with Order records (in the ORDER table), which includes LineItem records (in a LINE_ITEM table). I'm looking ...

4. problem with subqueries    forum.hibernate.org

This is my original HQL query which works just fine. Code: protected static final String HQL_FIND_LATEST_DOCS = "from " + DOC_REFERENCE_TABLE_NAME + " as docRef1 where docRef1.recipientAccount in (:accounts) and docRef1.ticketNumber = (" + " select max(docRef2.ticketNumber) from " + DOC_REFERENCE_TABLE_NAME ...

5. using Subqueries.in to emulate union    forum.hibernate.org

Hibernate version: 3.1.2.ga Hello, I've got a legacy database with a central "Client" table that has 2 exclusive FKeys to "Relex" and "NonRef". I'd like to search in both tables for similar criterias. I've build a DetachedCriteria that works on both tables. I now want to merge the results. As union is not supported, I'd like to use an "in" construct ...

6. Subqueries    forum.hibernate.org

Hi, I have just started working on Hibernate. I have a doubt about subqueries. My query in Oracle is select * from ( select * from tabelname where ID=12 order by dtCreation desc ) where rownum <= 4; Please let me know how I can use the query in hibernate. Thanks in advance. Sri.

7. subQuery    forum.hibernate.org

Hi I would like create SELECT that uses table in sub Query. But I have no Idea how to do that. I tryed that but it does not work: Code: SELECT ...

8. subquery 2 levels    forum.hibernate.org

Hi! This is probably a trivial question... I have a Ticket object which hold Price objects (one-to-Many), Price objects hold PriceLevel objects (one-to-Many). Trying to figure out how to select all tickets with a specific PriceLevel.pk. Selecting prices from ticket is no problem. crit.createAlias("prices", "pricesAlias"). setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY). add( Restrictions.between("pricesAlias.price", fromPrice, toPrice)); But have struggled with the priceLevels for quite some time. Would ...

9. Count (*) from subquery    forum.hibernate.org

Hi all, I'm trying to do a query to count the result of another query using HQL, but the system show a error message, this is the query and the error. SELECT COUNT(*) FROM (SELECT AudienceMeter a JOIN FETCH a.parameterList AS pl JOIN FETCH pl.parameter AS p JOIN FETCH p.command AS cmd) and the error is: unexpected token: ( near line ...





11. HQL subquery problem    forum.hibernate.org

12. Subquery in HQL    forum.hibernate.org

hi all, Im new to Hibernate . Im using Hibernate 3 Server : JBoss 4.0.2 This is my sql select userid,password,startdate,enddate,(select count(sessionid)+1 from tsession where ltrim(rtrim(userid))= '"+username.trim()+"') as sessioncount,wipuserid from users where ltrim(rtrim(userid)) ='"+username.trim()+"' and ltrim(rtrim(password))='"+password.trim()+"' This query woks fine. In this i have a SubQuery select count(sessionid)+1 from tsession where ltrim(rtrim(userid))= '"+username.trim()+"' without giving this subquery i have written in ...

13. detachedquery and subqueries    forum.hibernate.org

14. Selecting from a subquery    forum.hibernate.org

In principle, Hibernate supports both simple case ... when constructs and subqueries (if the underlying database supports them). So your query as it is should work. My advice is, try to simplify the query by creating a view with the case...when part, and o from there. In that way, you will know if the problem is on that nested group specifically, ...

15. subquery    forum.hibernate.org

16. HQL - subquery with update    forum.hibernate.org

update BI$pricing p set p.isDeleted = 'P' where p.id in (select p.id from BI$pricing p join p.folder f where f.contextId = :folderBoId and p.contextId = :pricingBoId and p.isDeleted = 'N' and f.isDeleted = 'N')





17. Subquery in the FROM clause with HQL    forum.hibernate.org

Hi all, Are this kind of queries supported by HQL? SELECT COUNT(*) FROM ( SELECT a, b, c, SUM(d) FROM T1, T2, ... WHERE ... GROUP BY a, b, c ) I am looking for an efficient way to count the results of my query, without having to say Hibernate to retrieve all the resulting rows. Thanks in advance :-) Rubn ...

18. using TOP in subquery    forum.hibernate.org

19. How to reference a superclass column in correlated subquery?    forum.hibernate.org

Hi, I am having difficulties getting Hibernate to use the correct table alias for a correlated subquery. In the example below, I want the correlated subquery in the formula for the lookthruTicker to reference the alias of the superclass (Security), not the outer class (Bond) for the owner_system_code column. Is there any way to reference the superclass? Can I set the ...

20. Usage of Subqueries and PropertyGt ??    forum.hibernate.org

Hi! I am trying to accomplish the following using criteria: select * from A where A.value < (select A.value from A where A.key='specific'); I would prefer to use the Criteria api because the left-hand query is actually much more complicated and already written in criteria. This is what I have tried but it does not seem to yield any results: Criteria ...

21. Problem using sum with a subquery    forum.hibernate.org

Hello! I have a problem with a query of mine. The Situation: 2 Entities, Person and Disease. Persons have: id, name, set of diseases Disease have: id, name, duration, and a referenced person. The Relationship is a 1:n relationship, one person can have multible diseases What i want to do is the following: I want (if it's possible in one query) ...

22. Hibernate subquery with two column    forum.hibernate.org

23. hql using subquery after from caluse    forum.hibernate.org

24. Criteria and subquery    forum.hibernate.org

Hi all, can I rewrite to "Hibernate Criteria" this SQL? SELECT u.id, p.num FROM user AS u LEFT JOIN (SELECT SUM(p.num) AS num, p.id FROM user_num AS p WHERE p.obd = '200802' GROUP BY p.id) AS p ON p.id = u.id WHERE ..... I have Hibernate 3.2.3 and MySQL 5.0. I know basic things with criteria as Subquery.xxx, but this feature ...

25. How to use a subquery in FROM clause    forum.hibernate.org

Hi, I have the following SQL query with a subquery in FROM clause : Code: select emp.roll_number, emp.first_name from employee emp, ( select elt.external_id from element_value_by_date evd inner join data d on d.id = evd.data_fk inner join element elt on elt.id = evd.element_fk ...

26. Subqueries within Subqueries    forum.hibernate.org

I have 4 detachedcriteria (A, B, C, and D) C is a subquery of A D is a subquery of B It would be nice to just run a query with A = B, but I don't know how to do that within 1 query, and detachedcriteria equal to eachother. So I thought I would just say B is a subquery ...

27. I miss join+subquery    forum.hibernate.org

28. why hibernate criteria constructs subquery    forum.hibernate.org

Hi, i am using hibernate criteria for for my search, but when am seeing the query constructed by hibernate , its contain subquery. actually i created table, the table is actually view, in criteria just am passing the search params, but i dont have idea y its constructing subquery, is all the critera constructs subquery? like this select * from ( ...

29. Self-join vs. Subquery    forum.hibernate.org

Hibernate version: 3.1.3 Hello all, I'ld like to hear your opinions and/or suggestions on the following situations. Thanks a lot in advance! 1.) select * from customer x, customer y WHERE x.lastname = y.lastname AND y.id = '4711' I would like to get all customers from a table "customer" which have the same lastname as the customer with the id "4711". ...

30. joining on a subquery ?    forum.hibernate.org

hi i am trying to solve the following problem i have a list of messages and a message has a sender for each sender i would like to list the amount of messages this person has posted in total ideally i would have this be a single query. and in sql this would be possible with something like the following code ...

31. Hibernate criteria subquery    forum.hibernate.org

32. HQL - Native SQL subqueries    forum.hibernate.org

33. Help with Subquery    forum.hibernate.org

Hello. I'm working with Hibernate 3 and Java 1.4. I have a working subquery in a junit test. The problem comes when I try to run the exact same code in my spring app. I have other hibernate queries and criteria working just fine, and its using the same jar files and the same hibernate.cfg.xml. I've manually checked to make sure ...

34. HQL with subquery problem    forum.hibernate.org

35. IsEmpty clause with subquery    forum.hibernate.org

36. unnecessary join in subquery    forum.hibernate.org

37. SubQuery in Hibernate    forum.hibernate.org

38. Criteria Query N+1 Collection Subqueries, HQL Runs 1 Query    forum.hibernate.org

Author Message mephesto Post subject: Criteria Query N+1 Collection Subqueries, HQL Runs 1 Query Posted: Mon Sep 21, 2009 6:53 pm Newbie Joined: Fri Jan 23, 2009 8:01 pm Posts: 13 I have a rather complex query that I wrote initially in HQL and found I had fetching problems. I solved those and the original HQL query runs and ...

39. problem with subqueries    forum.hibernate.org

40. JPA Joins with subquery    forums.oracle.com

I am using JP QL to write my queries. I have a SQL as SELECT * FROM `powergenerationdatapoint` AS pgdp INNER JOIN ( SELECT MAX( TIMESTAMP ) TIME FROM powergenerationdatapoint GROUP BY solarpanel_id )maxtime ON pgdp.timestamp = maxtime.time WHERE solarpanel_id IN ( SELECT id FROM solarpanel WHERE solar_plant_id =1 ) GROUP BY solarpanel_id iI am joining here on a subquery. Can ...