Skip to Content

Multiple Results In Query

Hello Experts

I have a query and I seem to have a problem with it

The problem is I am getting multiple results when I run it

Can someone please have a look and advise where it's wrong and cause it to give multiple results

The query is as below

SELECT T0.[DocNum] as 'Prod Order #',

T0.[U_CustName],

T0.[PlannedQty] as 'Qty',

T0.[ItemCode],

T2.[ItemName] as 'Product Description',

T1.[PlannedQty],

T0.[Comments]

FROM OWOR T0  INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode

WHERE T1.[ItemCode]  <> 'SUNDRY LABOUR' and t1.docentry NOT IN ( select t1.docentry from wor1 t1 where t1.itemcode = 'SUNDRY LABOUR') and T1.[ItemCode]  <> 'OUTSOURCED' and t1.docentry NOT IN ( select t1.docentry from wor1 t1 where t1.itemcode = 'OUTSOURCED') and 

T0.[Status] not in ( 'C','L') and  T0.[DueDate] between [%0] and [%1] 

GROUP  by T0.[DocNum],

T0.[U_CustName],

T0.[PlannedQty],

T0.[ItemCode],

T2.[ItemName],

T1.[PlannedQty],

T0.[Comments]

ORDER BY T0.[DocNum]

In the result of the query

I am getting multiple results for the WOR1 [PlannedQty] column

I think the query is getting confused with the OWOR [PlannedQty] & the WOR1 [PlannedQty]

Any suggestion on how I can fix this issue?

Thanks and Regards

Rahul

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jun 01, 2015 at 04:47 AM

    Hi rahul,

    You need Finished goods qty and sum of raw materials planned qty??

    If yes, you can use below query. or please tell exact requirement.

    SELECT T0.[DocNum] as 'Prod Order #',

    T0.[U_CustName],

    T0.[PlannedQty] as 'Qty',

    T0.[ItemCode],

    T2.[ItemName] as 'Product Description',

    Sum(T1.[PlannedQty])[RowPlannedQty],

    T0.[Comments]

    FROM OWOR T0 

      INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

      INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode

    WHERE T1.[ItemCode] not in ('SUNDRY LABOUR' ,'OUTSOURCED')

      and t1.docentry NOT IN

      ( select t1.docentry

      from wor1 t1

      where T1.itemcode = 'SUNDRY LABOUR' or T1.itemcode ='OUTSOURCED')

      and T0.[Status] not in ( 'C','L')

      and  T0.[DueDate] between [%0] and [%1]

      --and T1.[ItemCode]  <> 'OUTSOURCED'

      --and t1.docentry NOT IN ( select t1.docentry from wor1 t1 where t1.itemcode = 'OUTSOURCED')

    GROUP  by T0.[DocNum],

    T0.[U_CustName],

    T0.[PlannedQty],

    T0.[ItemCode],

    T2.[ItemName],

    T0.[Comments]

    Thanks

    Unnikrishnan

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Rahul Fern

      Hi Rahul,

      I would like to answer your question why there's a multiple line shown on your query, this is because of WOR1.

      WOR1 table is the lists of items/child item for your BOM, therefore if you query OWOR and WOR1 together and show the result of both planned qty of OWOR and WOR1 you will have a multiple lines, if you want to check it, try to show WOR1.itemcode.

      And please be more specific what you really want to query. Because you're statement is a bit confusing versus to your query.

      Regards,
      Lean

  • Jun 01, 2015 at 11:28 AM

    Try This

    SELECT T0.[DocNum] as 'Prod Order #',

    T0.[U_CustName],

    T0.[PlannedQty] as 'Qty',

    T0.[ItemCode],

    T2.[ItemName] as 'Product Description',

    (select T1.[PlannedQty] from  WOR1 T1 where  T0.DocEntry = T1.DocEntry

    and  T1.[ItemCode]  <> 'SUNDRY LABOUR' and t1.docentry NOT IN ( select t1.docentry from wor1 t1 where t1.itemcode = 'SUNDRY LABOUR')

    and T1.[ItemCode]  <> 'OUTSOURCED' and t1.docentry NOT IN ( select t1.docentry from wor1 t1 where t1.itemcode = 'OUTSOURCED')),

    T0.[Comments]

    FROM OWOR T0  INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode where 

    T0.[Status] not in ( 'C','L') and  T0.[DueDate] between [%0] and [%1]

    GROUP  by T0.[DocNum],

    T0.[U_CustName],

    T0.[PlannedQty],

    T0.[ItemCode],

    T2.[ItemName],

    T1.[PlannedQty],

    T0.[Comments]

    ORDER BY T0.[DocNum]

    Add comment
    10|10000 characters needed characters exceeded

    • Try This

      SELECT T0.[DocNum] AS 'Prod Order #',

             ----T0.[U_CustName],

             T0.[PlannedQty] AS 'Qty',

             T0.[ItemCode],

             T2.[ItemName] AS 'Product Description',

             (

                 SELECT T1.[PlannedQty]

                 FROM   WOR1 T1

                 WHERE  T0.DocEntry = T1.DocEntry

                        AND T1.[ItemCode] <> 'SUNDRY LABOUR'

                        AND t1.docentry NOT IN (SELECT t1.docentry

                                                FROM   wor1 t1

                                                WHERE  t1.itemcode = 'SUNDRY LABOUR')

                        AND T1.[ItemCode] <> 'OUTSOURCED'

                        AND t1.docentry NOT IN (SELECT t1.docentry

                                                FROM   wor1 t1

                                                WHERE  t1.itemcode = 'OUTSOURCED')

             ),

             T0.[Comments]

      FROM   OWOR T0

             INNER JOIN OITM T2

                  ON  T0.ItemCode = T2.ItemCode

      WHERE  T0.[Status] NOT IN ('C', 'L')

           

             -----and  T0.[DueDate] between [%0] and [%1]

      ORDER BY

             T0.[DocNum]

  • avatar image
    Former Member
    Jun 01, 2015 at 08:13 PM

    Hi Rahul,

    Try:

    SELECT T0.[DocNum] as 'Prod Order #',

    T0.[U_CustName],

    T0.[PlannedQty] as 'Qty',

    T0.[ItemCode],

    T2.[ItemName] as 'Product Description',

    MAX(T1.[PlannedQty]) 'Labour hours'

    T0.[Comments]

    FROM OWOR T0

    INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry AND t1.itemcode = 'LABOUR'

    INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode

    WHERE T0.[Status] not in ( 'C','L') and  T0.[DueDate] between [%0] and [%1]

    GROUP  by T0.[DocNum],

    T0.[U_CustName],

    T0.[PlannedQty],

    T0.[ItemCode],

    T2.[ItemName],

    T0.[Comments]

    ORDER BY T0.[DocNum]

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Gordon

      It kinda works and I think I should have been more specific

      The results from your query display all the production orders scheduled for the day with the correct Labour hours, but wont display if there isn't any labour.

      Sometimes we have production orders that are accessories and don't have a labour

      Would it be possible to get the production order with labour and without labour to be displayed in the result?

      Can is use CASE WHEN T1.[PlannedQty]  <> ‘Labour Hours’ THEN 0

      Thanks and Regards

      Rahul