Skip to Content
Former Member
Jun 01, 2008 at 12:26 PM

Help In the Query


Hi Guys,

In the general settings- Display window. I have set the decimal places in amount field to zero and quantity to 3. In financial setup-currencies INR is rounded to one. Now i have written the following query to check the daily production.

Query- Select Distinct X.Docnum As Prod_Ordr_no, X.U_Lot as Lot_No, Y.Postdate As Prod_Ord_Date, X.Product, X.Product_qty, Y.Itemcode AS Raw_Material, Y.Raw_Material_Qty, Z.Scrap_Qty, X.Loss


(Select A.docnum, A.U_Lot, A.itemcode as Product,

Avg(A.Cmpltqty) as Product_qty, sum(B.issuedQty)- avg(A.cmpltqty) as Loss

from WOR1 B, owor A

where B.docentry=a.docentry and A.status != 'C' and A.CmpltQty > 0

group by A.Docnum, A.itemcode, A.U_Lot) As X INNER JOIN

(select A.docnum, A.Postdate, B.itemcode, b.Issuedqty as Raw_Material_Qty from OWOR A, WOR1 B

where A.docentry = B.docentry and b.itemcode IN ('RINGT854','RBILT856','RSQUR-1') and A.status != 'C' and B.issuedqty > 0)

As Y ON X.Docnum = Y.Docnum left outer join

(Select A.Docnum, -B.IssuedQty As Scrap_Qty from OWOR A, WOR1 B where A.Docentry = B.Docentry

and B.itemcode = 'FSCRP861' and A.Status !='C') As Z on Y.Docnum = Z.Docnum

Order by X.Docnum, Y.postdate

The query result is datewise, quantity of product, quantity of raw material, quantity of scrap (scrap is an item and is taken into BOM), Loss occured during production ( loss = Issued qty-(product qty + Scrap))

The problem is the quantity of scrap and loss comes in round figue while It is required in 3 decimal places. Now If i set amount display field to 3 decimapl places. It shows me the desired result. But that is impossible because of other implications. Can any one help me to set a query in a such a manner that even after setting the amount display field to 0 decimal places, It will show me scrap and loss quantity in 3 decimal places.