select 0 as putinlibraryID,
'合计' as putincode,
null as productcode,
null as productname,
null as provider,
null as spec,
null as putincode,
null as unit,
null as putindate,
sum(putin.factnum) as factnum,
sum(putin.budget) as budget,
sum(putin.pactprice) as pactprice
from project_putindepot putin where
nvl(putin.id,0) >= '0' and not (nvl(putin.id,0) > '99999999999999999')
and nvl(putin.productname,' ') like '%%'
and nvl(putin.productcode,' ') like '%%'
and nvl(putin.spec,' ') like '%%'
and nvl(putin.pactcode,' ') like '%%'
and nvl(putin.provider,' ') like '%%'
and nvl(putin.putincode,' ') like '%%'
and ((putin.putindate >= to_date('1900-01-01','yyyy-MM-dd')) and not(putin.putindate > to_date('2020-01-01','yyyy-MM-dd')) or putin.putindate is null)union select putin.id as putinlibraryID,
putin.putincode as putincode,
putin.productcode as productcode,
putin.productname as productname,
putin.provider as provider,
putin.spec as spec,
putin.putincode as putincode,
putin.unit as unit,
to_char(putin.putindate,'yyyy-MM-dd') as putindate,
putin.factnum as factnum,
putin.budget as budget,
putin.pactprice as pactprice
from project_putindepot putin where
nvl(putin.id,0) >= '0' and not (nvl(putin.id,0) > '999999999999999')
and nvl(putin.productname,' ') like '%%'
and nvl(putin.productcode,' ') like '%%'
and nvl(putin.spec,' ') like '%%'
and nvl(putin.pactcode,' ') like '%%'
and nvl(putin.provider,' ') like '%%'
and nvl(putin.putincode,' ') like '%%'
and ((putin.putindate >= to_date('1900-01-01','yyyy-MM-dd')) and not(putin.putindate > to_date('2020-01-01','yyyy-MM-dd')) or putin.putindate is null)
order by putindate desc
------------------------------------------------------------------------------
以上是我的sql语句,在PlSql里运行正常,但是放到程序中的时候提示ORA-00918: 未明确定义列,java程序里用的是hibernate执行,是我的sql语句有问题,还是hibernate不能这样写
'合计' as putincode,
null as productcode,
null as productname,
null as provider,
null as spec,
null as putincode,
null as unit,
null as putindate,
sum(putin.factnum) as factnum,
sum(putin.budget) as budget,
sum(putin.pactprice) as pactprice
from project_putindepot putin where
nvl(putin.id,0) >= '0' and not (nvl(putin.id,0) > '99999999999999999')
and nvl(putin.productname,' ') like '%%'
and nvl(putin.productcode,' ') like '%%'
and nvl(putin.spec,' ') like '%%'
and nvl(putin.pactcode,' ') like '%%'
and nvl(putin.provider,' ') like '%%'
and nvl(putin.putincode,' ') like '%%'
and ((putin.putindate >= to_date('1900-01-01','yyyy-MM-dd')) and not(putin.putindate > to_date('2020-01-01','yyyy-MM-dd')) or putin.putindate is null)union select putin.id as putinlibraryID,
putin.putincode as putincode,
putin.productcode as productcode,
putin.productname as productname,
putin.provider as provider,
putin.spec as spec,
putin.putincode as putincode,
putin.unit as unit,
to_char(putin.putindate,'yyyy-MM-dd') as putindate,
putin.factnum as factnum,
putin.budget as budget,
putin.pactprice as pactprice
from project_putindepot putin where
nvl(putin.id,0) >= '0' and not (nvl(putin.id,0) > '999999999999999')
and nvl(putin.productname,' ') like '%%'
and nvl(putin.productcode,' ') like '%%'
and nvl(putin.spec,' ') like '%%'
and nvl(putin.pactcode,' ') like '%%'
and nvl(putin.provider,' ') like '%%'
and nvl(putin.putincode,' ') like '%%'
and ((putin.putindate >= to_date('1900-01-01','yyyy-MM-dd')) and not(putin.putindate > to_date('2020-01-01','yyyy-MM-dd')) or putin.putindate is null)
order by putindate desc
------------------------------------------------------------------------------
以上是我的sql语句,在PlSql里运行正常,但是放到程序中的时候提示ORA-00918: 未明确定义列,java程序里用的是hibernate执行,是我的sql语句有问题,还是hibernate不能这样写
解决方案 »
- 创建表空间出错
- 关于Oracle中存储过程的问题,执行总报错,有时编译不成功,使用工具是PLSQL
- export一个大表时报oracle-01555错误如何解决
- 从表中筛选记录 并按物料号分类,分析
- oracle性能优化,SQL语句全优化,SQL语句执行效率问题的等深度分析文档
- oracle10设了一下崩溃了,ORA-00371: not enough shared pool memory。想手工修改,配置文件找不到是哪个,大虾急救。
- 数据库定时器
- 关于大数据量的实时数据库表设计问题
- 国庆紧急求救:oracle 8i的服务启动问题
- 请问如何让一个存储过程在一个特定时间自动执行
- 查询问题
- 100分求教 一个update语句(C# 写Oracle数据库中的BLOB类型字段)
SELECT 0 AS putinlibraryID,
'合计' AS putincode_sum,
NULL AS productcode,
NULL AS productname,
NULL AS provider,
NULL AS spec,
NULL AS putincode,
NULL AS unit,
NULL AS putindate,
SUM(putin.factnum) AS factnum,
SUM(putin.budget) AS budget,
SUM(putin.pactprice) AS pactprice
FROM project_putindepot putin
WHERE nvl(putin.id, 0) >= '0'
AND NOT (nvl(putin.id, 0) > '99999999999999999')
AND nvl(putin.productname, ' ') LIKE '%%'
AND nvl(putin.productcode, ' ') LIKE '%%'
AND nvl(putin.spec, ' ') LIKE '%%'
AND nvl(putin.pactcode, ' ') LIKE '%%'
AND nvl(putin.provider, ' ') LIKE '%%'
AND nvl(putin.putincode, ' ') LIKE '%%'
AND ((putin.putindate >= to_date('1900-01-01', 'yyyy-MM-dd')) AND
NOT (putin.putindate > to_date('2020-01-01', 'yyyy-MM-dd')) OR
putin.putindate IS NULL)
UNION
SELECT putin.id AS putinlibraryID,
putin.putincode AS putincode_sum,
putin.productcode AS productcode,
putin.productname AS productname,
putin.provider AS provider,
putin.spec AS spec,
putin.putincode AS putincode,
putin.unit AS unit,
to_char(putin.putindate, 'yyyy-MM-dd') AS putindate,
putin.factnum AS factnum,
putin.budget AS budget,
putin.pactprice AS pactprice
FROM project_putindepot putin
WHERE nvl(putin.id, 0) >= '0'
AND NOT (nvl(putin.id, 0) > '999999999999999')
AND nvl(putin.productname, ' ') LIKE '%%'
AND nvl(putin.productcode, ' ') LIKE '%%'
AND nvl(putin.spec, ' ') LIKE '%%'
AND nvl(putin.pactcode, ' ') LIKE '%%'
AND nvl(putin.provider, ' ') LIKE '%%'
AND nvl(putin.putincode, ' ') LIKE '%%'
AND ((putin.putindate >= to_date('1900-01-01', 'yyyy-MM-dd')) AND
NOT (putin.putindate > to_date('2020-01-01', 'yyyy-MM-dd')) OR
putin.putindate IS NULL)
ORDER BY putindate DESC