hql语句
1.select sum(case when t.accountinoutDirection = 0 then 1 else -1 end) * t.accountinoutMoney) as startmoney, t.inneraccountsCode,t.orgCode from FundAccountinout t
group by t.inneraccountsCode,t.orgCode2.select sum(case when to_char(t.accountinoutDate,'yyyymmdd hh24:mi:ss') < to_char(to_date('2010-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd hh24:mi:ss') then (case when t.accountinoutDirection = 0 then 1 else -1 end) * t.accountinoutMoney end) as startmoney, t.inneraccountsCode,t.orgCode from FundAccountinout t
group by t.inneraccountsCode,t.orgCode
1 可以执行,2报错误如下
09:47:47,484 INFO [STDOUT] 2010-08-02 09:47:47,484 ERROR [org.hibernate.hql.PARSER] - line 1:228: unexpected token: *
09:47:47,484 INFO [STDOUT] 2010-08-02 09:47:47,484 ERROR [org.hibernate.hql.PARSER] - line 1:250: expecting CLOSE, found 'end'
09:47:47,484 INFO [STDOUT] 2010-08-02 09:47:47,484 WARN [org.hibernate.hql.ast.HqlParser] - processEqualityExpression() : No expression to process!
09:47:47,500 ERROR [[default]] Servlet.service() for servlet default threw exception
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: * near line 1, column 228 [select sum(case when to_char(t.accountinoutDate,'yyyymmdd hh24:mi:ss') < to_char(to_date('2010-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd hh24:mi:ss') then (case when t.accountinoutDirection = 0 then 1 else -1 end) * t.accountinoutMoney end) as startmoney, t.inneraccountsCode,t.orgCode from com.golden.soft.model.fund.FundAccountinout t where to_char(t.accountinoutDate,'yyyymmdd hh24:mi:ss') <= to_char(to_date('2010-08-02 00:00:00','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd hh24:mi:ss') and t.memberCode = ? group by t.inneraccountsCode,t.orgCode]
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)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1650)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:923)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:921)
at com.golden.soft.common.system.dao.HibernateGenericDao.getResultCount(HibernateGenericDao.java:635)
at com.golden.soft.common.system.dao.HibernateGenericDao.queryForPageByHql(HibernateGenericDao.java:330)上次发的not mapped的错误是应为FundAccountinout 对向名字写错了,有区分大小写
1.select sum(case when t.accountinoutDirection = 0 then 1 else -1 end) * t.accountinoutMoney) as startmoney, t.inneraccountsCode,t.orgCode from FundAccountinout t
group by t.inneraccountsCode,t.orgCode2.select sum(case when to_char(t.accountinoutDate,'yyyymmdd hh24:mi:ss') < to_char(to_date('2010-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd hh24:mi:ss') then (case when t.accountinoutDirection = 0 then 1 else -1 end) * t.accountinoutMoney end) as startmoney, t.inneraccountsCode,t.orgCode from FundAccountinout t
group by t.inneraccountsCode,t.orgCode
1 可以执行,2报错误如下
09:47:47,484 INFO [STDOUT] 2010-08-02 09:47:47,484 ERROR [org.hibernate.hql.PARSER] - line 1:228: unexpected token: *
09:47:47,484 INFO [STDOUT] 2010-08-02 09:47:47,484 ERROR [org.hibernate.hql.PARSER] - line 1:250: expecting CLOSE, found 'end'
09:47:47,484 INFO [STDOUT] 2010-08-02 09:47:47,484 WARN [org.hibernate.hql.ast.HqlParser] - processEqualityExpression() : No expression to process!
09:47:47,500 ERROR [[default]] Servlet.service() for servlet default threw exception
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: * near line 1, column 228 [select sum(case when to_char(t.accountinoutDate,'yyyymmdd hh24:mi:ss') < to_char(to_date('2010-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd hh24:mi:ss') then (case when t.accountinoutDirection = 0 then 1 else -1 end) * t.accountinoutMoney end) as startmoney, t.inneraccountsCode,t.orgCode from com.golden.soft.model.fund.FundAccountinout t where to_char(t.accountinoutDate,'yyyymmdd hh24:mi:ss') <= to_char(to_date('2010-08-02 00:00:00','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd hh24:mi:ss') and t.memberCode = ? group by t.inneraccountsCode,t.orgCode]
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)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1650)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:923)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:921)
at com.golden.soft.common.system.dao.HibernateGenericDao.getResultCount(HibernateGenericDao.java:635)
at com.golden.soft.common.system.dao.HibernateGenericDao.queryForPageByHql(HibernateGenericDao.java:330)上次发的not mapped的错误是应为FundAccountinout 对向名字写错了,有区分大小写
解决方案 »
- 查询sql语句,急急急!
- vb.net中如何让DataGridView1控件一次显示多个表?
- 如何将学生选课信息另存成事务类型记录的数据裱??????
- 急问!!!关于Null的Join 操作
- 这个权限有哪些作用(哪些功能啊)grant resource to test;可以得到哪些权限呢,如insert 之类吗?
- 我要在数据库里放byte型数据
- 在p4上安装oracle8.1.7后,老是提示java.exe出错,请问如后解决
- oracle 的nvl函数结果问题
- ORA-00445: background process W000 did not start after 120 seconds导致数据库死机
- oracle 11G 日志归档问题
- 关于动态拼接分区名,然后查询的问题
- Oracle 9i Application Server
语句2没发现错误,其它的问一下了解hibernate的吧
then (case when t.accountinoutDirection = 0 then 1 else -1 end) * t.accountinoutMoney end) as startmoney,
t.inneraccountsCode,t.orgCode from FundAccountinout t
group by t.inneraccountsCode,t.orgCode
select sum((case when t.accountinoutDirection = 0 then 1 else -1 end) * t.accountinoutMoney ) as startmoney, t.inneraccountsCode,t.orgCode
from FundAccountinout t
where t.accountinoutDate<to_date('2010-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
group by t.inneraccountsCode,t.orgCode
wkc168:
我的需求是这样子的:时间段为3.1~7.1,需查出6列,期初金额(3.1号之前的余额),本期收入(3.1-7.1号的收入),本期支出(3.1~7.1的支出),期末金额(7.1号之前的余额),账号,机构
完整的hql为:
select sum(case when ( to_char(t.accountinoutDate,'yyyymmdd hh24:mi:ss') < to_char(to_date('2010-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd hh24:mi:ss')) then (case when t.accountinoutDirection = 0 then 1 else -1 end) * t.accountinoutMoney end) as 期初金额, sum(case when to_char(t.accountinoutDate,'yyyymmdd hh24:mi:ss') >= to_char(to_date('2010-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd hh24:mi:ss') and to_char(t.accountinoutDate,'yyyymmdd hh24:mi:ss') <= to_char(to_date('2010-08-02 00:00:00','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd hh24:mi:ss') then (case when t.accountinoutDirection = 0 then 1 else 0 end) * t.accountinoutMoney end) as 本期收入, sum(case when to_char(t.accountinoutDate,'yyyymmdd hh24:mi:ss') >= to_char(to_date('2010-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd hh24:mi:ss') and to_char(t.accountinoutDate,'yyyymmdd hh24:mi:ss') <= to_char(to_date('2010-08-02 00:00:00','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd hh24:mi:ss') then (case when t.accountinoutDirection = 0 then 0 else 1 end) * t.accountinoutMoney end) as 本期支出, sum(case when to_char(t.accountinoutDate,'yyyymmdd hh24:mi:ss') <= to_char(to_date('2010-08-02 00:00:00','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd hh24:mi:ss') then (case when t.accountinoutDirection = 0 then 1 else -1 end) * t.accountinoutMoney end) as 期末金额, t.inneraccountsCode,t.orgCode from FundAccountinout t where to_char(t.accountinoutDate,'yyyymmdd hh24:mi:ss') <= to_char(to_date('2010-08-02 00:00:00','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd hh24:mi:ss') and t.memberCode = ? group by t.inneraccountsCode,t.orgCode
O(∩_∩)O~,很多人估计跟我差不多,看到过长的没耐心,所以写了简短的
你把表结构也发一份来看看
SQL> select * from test;
ID NAME MEAN VALUE
--------------------- ---------- ---------- -----------
1 a b 1
2 a b 1
a c 2
SQL>
SQL> SELECT NAME, SUM(CASE WHEN NAME='a' THEN (CASE WHEN mean='b' THEN 1 ELSE 2 END)*VALUE END)
2 FROM test GROUP BY NAME
3 ;
NAME SUM(CASEWHENNAME='A'THEN(CASEW
---------- ------------------------------
a 6
SQL>
group by t.inneraccountsCode,t.orgCode
即红色部分加了个括号
⊙﹏⊙b汗,hql语句太严格了