数据库是oracleSELECT
(
SELECT SUM(originalvalue) FROM v_assetcard A
WHERE A.deptid=dept.ID
AND A.YEARMONTH=:begno
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.STATE IN ('Y','W','T','1','2','3','4')
) OriginalValue
FROM
department dept
GROUP BY dept.ID,dept.NAME说明:
originalvalue是v_assetcard的字段
字段对应的表都是没有错的在toad里报的错是"不是Group By的表达式"
(
SELECT SUM(originalvalue) FROM v_assetcard A
WHERE A.deptid=dept.ID
AND A.YEARMONTH=:begno
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.STATE IN ('Y','W','T','1','2','3','4')
) OriginalValue
FROM
department dept
GROUP BY dept.ID,dept.NAME说明:
originalvalue是v_assetcard的字段
字段对应的表都是没有错的在toad里报的错是"不是Group By的表达式"
FROM v_assetcard A,department dept
WHERE A.deptid=dept.ID
AND A.YEARMONTH=:begno
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.STATE IN ('Y','W','T','1','2','3','4')
GROUP BY dept.ID,dept.NAME
--猜的
SELECT SUM(originalvalue)
FROM v_assetcard A,department dept
WHERE A.deptid=dept.ID
AND A.YEARMONTH=:begno
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.STATE IN ('Y','W','T','1','2','3','4')
group by dept.ID,dept.NAME
FROM v_assetcard A, department dept
WHERE A.deptid = dept.ID
AND A.YEARMONTH = :begno
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.STATE IN ('Y', 'W', 'T', '1', '2', '3', '4')
GROUP BY dept.ID, dept.NAME
(
SELECT SUM(originalvalue) FROM v_assetcard A
WHERE A.deptid=dept.ID
AND A.YEARMONTH=:begno
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.STATE IN ('Y','W','T','1','2','3','4')
) OriginalValue,
(
SELECT nvl(sum(PRICE),0)
FROM BILLMAIN A, BILLDETAIL B
WHERE A.ID = B.ID AND A.CLASS = 'J' AND NO BETWEEN :begno || '0000' AND :endno || '9999' AND A.TYPEID IN
(SELECT ID FROM ASSETTYPE WHERE FIXED = 'N') AND A.SOURCEID=dept.ID
) DepValue
FROM
department dept
GROUP BY dept.ID,dept.NAME
FROM v_assetcard A,department dept
WHERE A.deptid=dept.ID
AND A.YEARMONTH=:begno
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.STATE IN ('Y','W','T','1','2','3','4')
group by dept.ID,dept.NAME
union all
SELECT nvl(sum(PRICE),0)
FROM BILLMAIN A, BILLDETAIL B
WHERE A.ID = B.ID AND A.CLASS = 'J' AND NO BETWEEN :begno || '0000' AND :endno || '9999' AND A.TYPEID IN
(SELECT ID FROM ASSETTYPE WHERE FIXED = 'N') AND A.SOURCEID=dept.ID
(
SELECT SUM(originalvalue) FROM v_assetcard A
WHERE A.deptid=dept.ID
AND A.YEARMONTH=:begno
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.STATE IN ('Y','W','T','1','2','3','4')
) OriginalValue,
(
(
SELECT SUM(originalvalue) FROM v_assetcard A
WHERE A.deptid=dept.ID
AND A.YEARMONTH=:begno
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.STATE IN ('Y','W','T','1','2','3','4')
)
-
(
SELECT nvl(sum(PRICE),0)
FROM BILLMAIN A, BILLDETAIL B
WHERE A.ID = B.ID AND A.CLASS = 'J' AND NO BETWEEN :begno || '0000' AND :endno || '9999' AND A.TYPEID IN
(SELECT ID FROM ASSETTYPE WHERE FIXED = 'N') AND A.SOURCEID=dept.ID
)
) DepValue
FROM
department dept
GROUP BY dept.ID,dept.NAME
FROM
(
SELECT dept.ID, dept.NAME,SUM(A.originalvalue) AS OriginalValue,0 AS PRICE
FROM v_assetcard A, department dept
WHERE A.deptid = dept.ID
AND A.YEARMONTH = :begno
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.STATE IN ('Y', 'W', 'T', '1', '2', '3', '4')
GROUP BY dept.ID, dept.NAME
UNION ALL
SELECT dept.ID, dept.NAME,0 AS OriginalValue, nvl(sum(PRICE), 0) AS PRICE
FROM BILLMAIN A, BILLDETAIL B,department dept
WHERE A.ID = B.ID
AND A.CLASS = 'J'
AND NO BETWEEN :begno || '0000' AND :endno || '9999'
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.SOURCEID = dept.ID
GROUP BY dept.ID, dept.NAME
) A
GROUP BY A.ID,A.NAME
SELECT SUM(A.originalvalue) AS OriginalValue, SUM(A.originalvalue1)-sum(A.PRICE) AS PRICE
FROM
(
SELECT dept.ID, dept.NAME,SUM(A.originalvalue) AS OriginalValue,0 AS PRICE,0 as originalvalue1
FROM v_assetcard A, department dept
WHERE A.deptid = dept.ID
AND A.YEARMONTH = :begno
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.STATE IN ('Y', 'W', 'T', '1', '2', '3', '4')
GROUP BY dept.ID, dept.NAME
union all
SELECT dept.ID, dept.NAME,0 as OriginalValue,0 as PRICE, SUM(originalvalue) as originalvalue1
FROM v_assetcard A,department dept
WHERE A.deptid=dept.ID
AND A.YEARMONTH=:begno
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.STATE IN ('Y','W','T','1','2','3','4')
GROUP BY dept.ID, dept.NAME
UNION ALL
SELECT dept.ID, dept.NAME,0 ASOriginalValue, nvl(sum(PRICE), 0) AS PRICE,0 as originalvalue1
FROM BILLMAIN A, BILLDETAIL B,department dept
WHERE A.ID = B.ID
AND A.CLASS = 'J'
AND NO BETWEEN :begno || '0000' AND :endno || '9999'
AND A.TYPEID IN (SELECT ID FROM ASSETTYPE WHERE FIXED = 'N')
AND A.SOURCEID = dept.ID
GROUP BY dept.ID, dept.NAME
) A
GROUP BY A.ID,A.NAME