Skip to Content
author's profile photo Former Member
Former Member

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

From

(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.

Thanks

Pardeep

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jun 02, 2008 at 05:57 AM

    Use convert function NUMERIC on field you want to get result in other decimal places.

    for 3 decimal places, use it as

    select convert(numeric(19,3), fieldname), ... from ...

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 09, 2008 at 10:35 AM

    hello janab

    ki haal hai apan vi forum ch aagye

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.