sirichai
Posts: 22
|
| Posted: 10/18/2006, 9:13 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
|
 |
 |
Edd
Posts: 547
|
| Posted: 10/19/2006, 1:56 AM |
|
What database are you using?
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
sirichai
Posts: 22
|
| Posted: 10/19/2006, 7:16 PM |
|
I use MS-SQL 2000.
Thanks
Sirichai
|
|
|
|
Edd
Posts: 547
|
| Posted: 10/20/2006, 8:25 PM |
|
You state that dates {frdate} and {todate} are floats? These should be dates whose format being passed to the database should be in the standard format 'yyyy-mm-dd'.
Also if you use dates with MSSQL they should have single quotes around them.
Without going into the sql it should be at least formatted like:
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
Hope that helps
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
sirichai
Posts: 22
|
| Posted: 10/20/2006, 8:53 PM |
|
thanks edd,
sirichai
|
|
|
|
|
