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不能这样写
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