05-10-2005 7:00 PM
Hi,
We are porting our codes to a 5.0 environment and getting the following error. The codes work fine in 4.0B.
Rajib
Error: The addition "FOR ALL ENTRIES" excludes all aggregate functions with the exception of "COUNT( * )" as the single element of the SELECT clause.
DATA: BEGIN OF IT_OBJNUM OCCURS 0,
COSTCTR LIKE CSKS-KOSTL,
OBJNR LIKE CSKS-OBJNR,
END OF IT_OBJNUM.
SELECT KSTAR OBJNR WRTTP SUM( WKG001 ) SUM( WKG002 ) SUM( WKG003 )SUM( WKG004 ) SUM( WKG005 ) SUM( WKG006 ) SUM( WKG007 )SUM( WKG008 ) SUM( WKG009 ) SUM( WKG010 ) SUM( WKG011 )SUM( WKG012 )
FROM COSP
INTO TABLE IT_BUDACT
FOR ALL ENTRIES IN IT_OBJNUM
WHERE OBJNR = IT_OBJNUM-OBJNR
AND GJAHR = P_YEAR
AND ( WRTTP = K_TYPE1 OR WRTTP = K_TYPE2 )
AND VERSN = K_VERSION
GROUP BY KSTAR OBJNR WRTTP.
05-10-2005 7:18 PM
This is right from the 640 online help:
<b>If the addition FOR ALL ENTRIES is used in front of WHERE, or if cluster or pool tables are listed after FROM, no other aggregate expressions apart from COUNT( * ) can be used.</b>
So it looks like this was an intentional change on SAP's part. I guess you will have to change your FOR ALL ENTRIES into the following:
DATA: BEGIN OF IT_OBJNUM OCCURS 0,
COSTCTR LIKE CSKS-KOSTL,
OBJNR LIKE CSKS-OBJNR,
END OF IT_OBJNUM.
loop at it_objnum.
SELECT KSTAR OBJNR WRTTP SUM( WKG001 ) SUM( WKG002 ) SUM( WKG003 )SUM( WKG004 ) SUM( WKG005 ) SUM( WKG006 ) SUM( WKG007 )SUM( WKG008 ) SUM( WKG009 ) SUM( WKG010 ) SUM( WKG011 )SUM( WKG012 )
FROM COSP
appending TABLE IT_BUDACT
*FOR ALL ENTRIES IN IT_OBJNUM
WHERE OBJNR = IT_OBJNUM-OBJNR
AND GJAHR = P_YEAR
AND ( WRTTP = K_TYPE1 OR WRTTP = K_TYPE2 )
AND VERSN = K_VERSION
GROUP BY KSTAR OBJNR WRTTP.
endloop.
I didn't actually try the code in my system, but I think this will have the same results.
05-10-2005 7:18 PM
This is right from the 640 online help:
<b>If the addition FOR ALL ENTRIES is used in front of WHERE, or if cluster or pool tables are listed after FROM, no other aggregate expressions apart from COUNT( * ) can be used.</b>
So it looks like this was an intentional change on SAP's part. I guess you will have to change your FOR ALL ENTRIES into the following:
DATA: BEGIN OF IT_OBJNUM OCCURS 0,
COSTCTR LIKE CSKS-KOSTL,
OBJNR LIKE CSKS-OBJNR,
END OF IT_OBJNUM.
loop at it_objnum.
SELECT KSTAR OBJNR WRTTP SUM( WKG001 ) SUM( WKG002 ) SUM( WKG003 )SUM( WKG004 ) SUM( WKG005 ) SUM( WKG006 ) SUM( WKG007 )SUM( WKG008 ) SUM( WKG009 ) SUM( WKG010 ) SUM( WKG011 )SUM( WKG012 )
FROM COSP
appending TABLE IT_BUDACT
*FOR ALL ENTRIES IN IT_OBJNUM
WHERE OBJNR = IT_OBJNUM-OBJNR
AND GJAHR = P_YEAR
AND ( WRTTP = K_TYPE1 OR WRTTP = K_TYPE2 )
AND VERSN = K_VERSION
GROUP BY KSTAR OBJNR WRTTP.
endloop.
I didn't actually try the code in my system, but I think this will have the same results.
05-10-2005 7:45 PM
Thomas,
Thanks for the solution.....after applying your ideas, I didn't get any compilation error...we will see how it does in runtime.
btw...how can I access "640 online help"?
Rajib
05-10-2005 8:01 PM
Hi Rajib,
You can access all the help at http://help.sap.com/
The 6.4 help specifically is at: http://help.sap.com/saphelp_erp2004/helpdata/en/e1/8e51341a06084de10000009b38f83b/frameset.htm
Cheers,
Brad
05-10-2005 8:05 PM
05-10-2005 10:39 PM
DATA: BEGIN OF IT_OBJNUM OCCURS 0,COSTCTR LIKE CSKS-KOSTL,OBJNR LIKE CSKS-OBJNR,END OF IT_OBJNUM.loop at it_objnum.SELECT KSTAR OBJNR WRTTP SUM( WKG001 ) SUM( WKG002 ) SUM( WKG003 )SUM( WKG004 ) SUM( WKG005 ) SUM( WKG006 ) SUM( WKG007 )SUM( WKG008 ) SUM( WKG009 ) SUM( WKG010 ) SUM( WKG011 )SUM( WKG012 )FROM COSPappending TABLE IT_BUDACT*FOR ALL ENTRIES IN IT_OBJNUMWHERE OBJNR = IT_OBJNUM-OBJNRAND GJAHR = P_YEARAND ( WRTTP = K_TYPE1 OR WRTTP = K_TYPE2 )AND VERSN = K_VERSIONGROUP BY KSTAR OBJNR WRTTP. endloop.
This would be one possible solution but I am afraid it is not the best solution.
Better for the system performance would be to read all records into an internal table at once and then do the LOOP and the SUM of the value fields. This is probably more efficient then to do a SELECT for every object in table IT_OBJNUM.
Michael