Multiple Results In Query

[RahF]

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

[Former Member]

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

[RahF]

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

[KennedyT21]

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]

[RahF]

Hi Kennedy

I tried your query I am getting the below error

Thanks and Regards

Rahul

[KennedyT21]

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]

Regards,

Kennedy

[RahF]

Hi Kennedy

I can't get it to work

I get an error 'Blanket Aggreement(OOAT)

Thanks and Regards

Rahul

[KennedyT21]

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]

[former_member212181]

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

[RahF]

Hi Unnikrishnan

This query is like a production schedule which can be run per day to check the number of production orders scheduled for the day, the quantity planned and the time required to build each WO.

We have a non-stock product code called Labour

This is in a BOM and when a production order is raised the production order qty is multiplied and we come up with a labour/time it will require to build

This is in the WOR1 table as PlannedQty, I need what ever is in this row to be displayed in the query result

At the moment the query result is displaying 11 rows for the same production order

Below is the production order screen

I want the query result to display the result as one row only

I can't figure out why the query is showing the result as 7 rows

Thanks and Regards

Rahul

[Former Member]

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