>I am trying to send select results to a grid, but the source data has decimal precision that I do not want to include. For example, the quantity field is defined in the table is a N(7,1). When I multiply it by a unit price, I get 3 places after my decimal. Here was my old code:
>
> Select invpost.custno as [Customer], invpost.company as [Company], Sum(invitempost.qtyord) As [Ord], Sum(invitempost.qtyshp) As [Ship], ;
> sum(invitempost.qtybo) As [BO], Sum(invitempost.qtyord * invitempost.unitpr) As [Ext_Price], ;
> count(Distinct invpost.invno) As [Orders], Count(*) As [Lines] ;
> FROM invpost INNER Join invitempost ;
> ON invpost.invno = invitempost.invno ;
> INTO Cursor curResults ;
> &lcwhere ;
> GROUP By Customer,Company ;
> ORDER By Ord DESC
>
>This works fine, but I get the bad decimals. So I thought I would create a cursor with data types and insert into it.
>
>CREATE CURSOR curResults (Customer C(12), Company C(30), Ord N(7), Ship N(7), BO N(7), Ext_Price N(8.2), Orders I, Lines I)
>INSERT INTO curResults (Customer, Company, Ord, Ship, BO, Ext_price, Orders, Lines) ;
> Select invpost.custno as [Customer], invpost.company as [Company], Sum(invitempost.qtyord) As [Ord], Sum(invitempost.qtyshp) As [Ship], ;
> sum(invitempost.qtybo) As [BO], Sum(invitempost.qtyord * invitempost.unitpr) As [Ext_Price], ;
> count(Distinct invpost.invno) As [Orders], Count(*) As [Lines] ;
> FROM invpost INNER Join invitempost ;
> ON invpost.invno = invitempost.invno ;
> &lcwhere ;
> GROUP By Customer,Company ;
> ORDER By Ord DESC
>
>This only seems to give me one record. Any ideas?
>
Randy,
You can use the ROUND() function in your query. This
should fix the problem, but it needs to be in both the statement itself, the where and group by clauses.
George
Ubi caritas et amor, deus ibi est