Results 1 to 4 of 4
  1. #1
    alvin0618 is offline Member
    Join Date
    Mar 2011
    Posts
    5
    Rep Power
    0

    Default SQL query to HQL

    Hi guys, I need to insert data from a table to another table.
    In SQL query, it is like these
    Java Code:
    INSERT INTO MonthlySubscription
    (MSISDN, PKGNAME, REQUESTON, TOTAL)
           SELECT tbl.MSISDN, tbl.PKGNAME, tbl.REQUESTON, SUM(tbl.total) as TOTAL FROM
                   (SELECT MSISDN, PKGNAME, TRUNC(REQUESTON) as REQUESTON, COUNT(*) as total
                        FROM SUBSCRIPTION
                        WHERE TRUNC(REQUESTON) = '15-MAR-11'
                        GROUP BY MSISDN, PKGNAME, REQUESTON
                     UNION ALL
                       SELECT MSISDN, PKGNAME, TRUNC(REQUESTON) as REQUESTON, COUNT(*) as total
                       FROM SUBSCRIPTION_HISTORY
                       WHERE TRUNC(REQUESTON) = '15-MAR-11'
                       GROUP BY MSISDN, PKGNAME, REQUESTON
                   )tbl
           GROUP BY tbl.MSISDN, tbl.PKGNAME, tbl.REQUESTON
           ORDER BY tbl.PKGNAME ASC;

    I have three tables model in Java
    Java Code:
    public class MonthlySubscription implements Serializable{
    	private String msisdn;
    	private String packageName;
    	private Date requestOn;
    	private int total;
    .....
    }
    
    public class SubscriptionHistory {
    	private String msisdn;
    	private String packageName;
    	private Date requestOn;
    	private Date lastUpdate;
    ....
    }
    
    public class Subscription implements Serializable{
    
    	private String msisdn;
    	private String packageName;
    	private Date expireOn;;
    	private Date requestOn;
    	private Date lastUpdate;
    .......
    }

    I have tried in HQL but got error - unexpected token.
    Java Code:
    	public void insertDailySubscription(String day)throws DataAccessException{
    		Session session = null;
    		Query query = null;
    		try{
    			session = this.getSession();
    			String hql_query = 
    				"INSERT INTO MonthlySubscription " +
    				"(msisdn, packageName, requestOn, total) " +
    				"SELECT msisdn, packageName, requestOn, SUM(total) as total FROM " +
    				"(SELECT s.msisdn, s.packageName, s.requestOn, COUNT(*) as total " +
    				"FROM Subscription s " +
    				"WHERE TO_CHAR(s.requestOn,'YYYYMMDD') = '" + day + "' " +
    				"GROUP BY s.msisdn, s.packageName, s.requestOn " +
    				"UNION ALL " +
    				"SELECT sh.msisdn, sh.packageName, sh.requestOn, COUNT(*) as total " +
    				"FROM SubscriptionHistory sh " +
    				"WHERE TO_CHAR(sh.requestOn,'YYYYMMDD') = '" + day + "' " +
    				"GROUP BY sh.msisdn, sh.packageName, sh.requestOn ) " +
    				"GROUP BY msisdn, packageName, requestOn " +
    				"ORDER BY packageName ASC";
    			
    			query = session.createQuery(hql_query);
    			query.executeUpdate();
    		}
    		catch(DataAccessException ex){
    			ex.printStackTrace();
    			throw ex;
    		}
    		catch(Exception ex){
    			ex.printStackTrace();
    		}
    		finally{
    			session.disconnect();
    		}
    	}
    Someone please safe me. ~~ TQ
    Last edited by alvin0618; 04-07-2011 at 11:00 AM.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,863
    Rep Power
    19

    Default

    Print out the string for your query.

    Also you should give us the full exception, not just your summary.

  3. #3
    alvin0618 is offline Member
    Join Date
    Mar 2011
    Posts
    5
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    Print out the string for your query.

    Also you should give us the full exception, not just your summary.
    Query:
    Java Code:
    INSERT INTO MonthlySubscription (msisdn, packageName, requestOn, total) SELECT msisdn, packageName, requestOn, SUM(total) as total FROM (SELECT s.msisdn, s.packageName, s.requestOn, COUNT(*) as total FROM Subscription s WHERE TO_CHAR(s.requestOn,'YYYYMMDD') = '20110407' GROUP BY s.msisdn, s.packageName, s.requestOn UNION ALL SELECT sh.msisdn, sh.packageName, sh.requestOn, COUNT(*) as total FROM SubscriptionHistory sh WHERE TO_CHAR(sh.requestOn,'YYYYMMDD') = '20110407' GROUP BY sh.msisdn, sh.packageName, sh.requestOn ) GROUP BY msisdn, packageName, requestOn ORDER BY packageName ASC
    Error:
    Java Code:
    org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 137 [INSERT INTO MonthlySubscription (msisdn, packageName, requestOn, total) SELECT msisdn, packageName, requestOn, SUM(total) as total FROM (SELECT s.msisdn, s.packageName, s.requestOn, COUNT(*) as total FROM com.model.Subscription s WHERE TO_CHAR(s.requestOn,'YYYYMMDD') = '20110407' GROUP BY s.msisdn, s.packageName, s.requestOn UNION ALL SELECT sh.msisdn, sh.packageName, sh.requestOn, COUNT(*) as total FROM com.syniverse.spsg.model.SubscriptionHistory sh WHERE TO_CHAR(sh.requestOn,'YYYYMMDD') = '20110407' GROUP BY sh.msisdn, sh.packageName, sh.requestOn ) GROUP BY msisdn, packageName, requestOn ORDER BY packageName ASC]
    	at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
    	at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
    	at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
    	at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:281)
    	at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180)
    	at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:134)
    	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
    	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
    	at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
    08042011 14:27:24.343 [ERROR] [              PARSER] [      main] line 1:137: unexpected token: (
    08042011 14:27:24.343 [ERROR] [              PARSER] [      main] line 1:183: unexpected token: COUNT

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,863
    Rep Power
    19

    Default

    It doesn't like that second subquery.
    It's pointing at the start of it (the open bracket by the look of it).
    Can't say why I'm afraid.

    The only thing I can say is that that ORDER By clause is a waste of time.

Similar Threads

  1. hql query
    By murali23krishna in forum JDBC
    Replies: 1
    Last Post: 03-17-2011, 07:25 AM
  2. Sql Query
    By Nomad in forum JDBC
    Replies: 16
    Last Post: 02-20-2009, 01:58 PM
  3. Help in Query
    By geeta_ravikanti in forum JDBC
    Replies: 0
    Last Post: 03-31-2008, 01:16 PM
  4. Using sql:query tag
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 01-15-2008, 03:13 PM
  5. Using sql:query tag
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 01-14-2008, 09:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •