sirichai
Posts: 22
|
| Posted: 10/18/2006, 9:10 PM |
|
Hi,
I create the grid by using vitual builder grid. and select SQL for select a group summary of records like this sql.
SELECT ICHIST.LOCATION,RTRIM(ICITEM.CATEGORY) + '-' + ICITEM.SEGMENT4 AS CODE, ICITEM.COMMENT1,
SUM(CASE WHEN ICHIST.TRANSDATE < {frdate} THEN ICHIST.QUANTITY ELSE 0 END) AS BFQTY,
SUM(CASE WHEN ICHIST.TRANSDATE < {frdate} THEN ICHIST.SRCEXTCST ELSE 0 END) AS BFCOST,
SUM(CASE WHEN ICHIST.TRANSDATE >= {frdate} AND ICHIST.TRANSDATE <= {todate} THEN ICHIST.QUANTITY ELSE 0 END) AS MONTHQTY,
SUM(CASE WHEN ICHIST.TRANSDATE >= {frdate} AND ICHIST.TRANSDATE <= {todate} THEN ICHIST.SRCEXTCST ELSE 0 END) AS MONTHCOST,
SUM(CASE WHEN ICHIST.TRANSDATE <= {todate} THEN ICHIST.QUANTITY ELSE 0 END) AS ENDQTY,
SUM(CASE WHEN ICHIST.TRANSDATE < {frdate} THEN ICHIST.SRCEXTCST ELSE 0 END) AS ENDCOSTQTY
FROM ICITEM INNER JOIN
ICHIST INNER JOIN
HFAPACCode ON ICHIST.TRANSTYPE = HFAPACCode.FLDVAL INNER JOIN
ICILOC ON ICHIST.LOCATION = ICILOC.LOCATION AND ICHIST.ITEMNO = ICILOC.ITEMNO ON ICITEM.ITEMNO = ICHIST.ITEMNO
WHERE ICHIST.LOCATION between {frloc} AND {toloc} AND
ICHIST.ITEMNO between {fritemno} AND {toitemno}
GROUP BY ICHIST.LOCATION,RTRIM(ICITEM.CATEGORY) + '-' + ICITEM.SEGMENT4, ICITEM.COMMENT1
ORDER BY ICHIST.LOCATION,RTRIM(ICITEM.CATEGORY) + '-' + ICITEM.SEGMENT4, ICITEM.COMMENT1
It no records to select what wrong of this any body help!!. (But I think problem is came from Where step may be text selection)
(the parameter I declare are {frdate} float, {todate} Float, frloc text,toloc text, fritemno text,toitemno text)
Thanks,
Sirichai
|
 |
 |
|
