Query to check Urgent Production Orders

[RahF]

Hi Experts

I have written a query to check for urgent production order requests

It works fine when the production order is linked to a sales order, but as soon as a production order is raised on it's own and isnt linked to sales order

It doesnt appear in the query results?

Can someone please help me find where i am going wrong?

SELECT     


TOP (100) PERCENT 


T2.DueDate AS [Build Date], 


T1.ShipDate AS [Must Despatch], 


T0.DocNum AS [Sales Order], 


T0.CardName AS [Sales Partner],                       


T1.U_Prod_Order_No AS [Prodn.Order], 


T1.Quantity AS Qty, 


T1.ItemCode AS [Product Code], 


T1.Dscription AS [Product Description], 


T2.Comments


FROM         


dbo.ORDR AS T0 INNER JOIN dbo.RDR1 AS T1 ON T0.DocEntry = T1.DocEntry



INNER JOIN dbo.OWOR AS T2 ON ISNULL(T1.U_Prod_Order_No, - 1)
= T2.DocNum


WHERE     


(T2.Status IN ('P', 'R')) AND 


(T1.ShipDate <> CONVERT(DATETIME, '2033-03-03
00:00:00', 102)) AND 


(T1.ShipDate <> CONVERT(DATETIME, '2044-04-04
00:00:00', 102)) AND 


(T1.ShipDate <> CONVERT(DATETIME, '2033-03-04
00:00:00', 102)) AND 


(T1.ShipDate >= GETDATE() - 1) AND 


(T1.ShipDate <= GETDATE() + 20)


GROUP BY 


T0.DocNum, T1.ShipDate, T0.CardName, T1.U_Prod_Order_No,
T1.Quantity, T1.ItemCode, T1.Dscription, T2.Comments, T2.DueDate


HAVING      


(T2.Comments LIKE N'%%URGENT%%') OR


(T2.Comments LIKE N'%%urgent%%') OR


(T2.Comments LIKE N'%%PRIORITY%%')


ORDER BY [Must Despatch], [Sales Partner], [Sales Order]

Thanks and Regards

Rahul

[RahF]

Hi Guys

I have tried the using the LEFT OUTER JOINS, but the production order that is not linked to a sales order does not appear in the query results

Below is the query I have modified from the original I posted, this one uses Augusto's join which I had to change a bit because I was getting errors

SELECT     
TOP (100) PERCENT 
T2.DueDate AS [Build Date], 
T1.ShipDate AS [Must Despatch], 
T0.DocNum AS [Sales Order], 
T0.CardName AS [Sales Partnet], 
T1.U_Prod_Order_No AS [Prodn.Order], 
T1.Quantity AS Qty, 
T1.ItemCode AS [Item Code], 
T1.Dscription AS [Item Description], 
T2.Comments AS Remarks
FROM         
dbo.OWOR AS T2 LEFT OUTER JOIN dbo.RDR1 AS T1 ON ISNULL(T1.U_Prod_Order_No, - 1) = T2.DocNum 
LEFT OUTER JOIN dbo.ORDR AS T0 ON T0.DocEntry = T1.DocEntry
WHERE     
(T2.Status IN ('P', 'R')) AND (
(T1.ShipDate <> CONVERT(DATETIME, '2033-03-03 00:00:00', 102)) AND 
(T1.ShipDate <> CONVERT(DATETIME, '2044-04-04 00:00:00', 102))AND 
(T1.ShipDate <> CONVERT(DATETIME, '2033-03-04 00:00:00', 102)) AND 
(T1.ShipDate >= GETDATE() - 1) AND 
(T1.ShipDate <= GETDATE() + 14)
GROUP BY T2.DueDate, T1.ShipDate, T0.DocNum, T0.CardName, T1.U_Prod_Order_No, T1.Quantity, T1.ItemCode, T1.Dscription, T2.Comments
HAVING      
(T2.Comments LIKE N'%%URGENT%%') OR
(T2.Comments LIKE N'%%urgent%%') OR
(T2.Comments LIKE N'%%PRIORITY%%')
ORDER BY [Must Despatch], [Sales Order], [Sales Partnet]

Regards

Rahul

[kothandaraman_nagarajan]

Hi Rahul,

Let me know still this issue not solved.

Regards,

Nagarajan

[RahF]

Hi Nagarajan

No I am still unable to get the query results i am after

Regards

Rahul

[former_member186712]

Hi Tahul,

Where you have the FROM till the WHERE replace by

FROM

dbo.OWOR T2 left outer join dbo.RDR1 AS T1 ISNULL(T1.U_Prod_Order_No,- 1)= T2.DocNum left outer join OWOR T0 ON T0.DocEntry = T1.DocEntry

WHERE

Best regards,

Augusto

[RahF]

Hi Augusto

I tried the join you have suggested, but I am getting the below error

Error in join expression.

Unable to parse query text

Regards

Rahul

[former_member186712]

Hi,

Please post the full query statement.

[Johan_H]

Hi Rahul,

The problem is that you are starting your FROM clause with ORDR (sales orders).

You need to start with OWOR and then LEFT OUTER JOIN RDR1 and ORDR.

Regards,

Johan

[RahF]

Hi Johan

Thanks for the reply

I tried the below, but I am not getting any results

FROM         
dbo.OWOR INNER JOIN dbo.RDR1 ON dbo.OWOR.DocEntry = dbo.RDR1.DocEntry 
LEFT OUTER JOIN dbo.ORDR ON dbo.OWOR.DocEntry = dbo.ORDR.DocEntry

Any suggestions?

Regards

Rahul

[Johan_H]

Hi Rahul,

Both joins must be LEFT OUTER. By using INNER join you effectively filter out all records that are not in both tables.

FROM         
dbo.OWOR LEFT OUTER JOIN dbo.RDR1 ON dbo.OWOR.DocEntry = dbo.RDR1.DocEntry 
LEFT OUTER JOIN dbo.ORDR ON dbo.OWOR.DocEntry = dbo.ORDR.DocEntry

Regards,

Johan

[RahF]

Hi Johan

I tried the join you suggested, it picked up the production order that wasn't linked to a sales order, but then all the other information in the Select criteria has disappeared and shows as Null

There is something missing

Regards

Rahul

[Johan_H]

Hi Rahul,

That is correct. As there is no related sales order for the given row, fields from that table will return null. You can try to use the ISNULL() or COALESCE() functions to return another value in these cases.

Regards,

Johan

[RahF]

Hi Johan

There are related sales orders but if I am using the LEFT OUTER joins the data shows NULL and when I use the FROM

dbo.OWOR AS T2 LEFTOUTERJOIN dbo.RDR1 AS T1 ON ISNULL(T1.U_Prod_Order_No,-1)= T2.DocNum 
LEFTOUTERJOIN dbo.ORDR AS T0 ON T0.DocEntry = T1.DocEntry

I get all the data, but the production order that isn't linked to a sales order doesn't appear in the query results

Regards

Rahul

[Johan_H]

Hi Rahul,

Please try this first:

SELECT     
T2.DueDate AS [Build Date], 
T1.ShipDate AS [Must Despatch], 
T0.DocNum AS [Sales Order], 
T0.CardName AS [Sales Partnet], 
T1.U_Prod_Order_No AS [Prodn.Order], 
T1.Quantity AS Qty, 
T1.ItemCode AS [Item Code], 
T1.Dscription AS [Item Description], 
T2.Comments AS Remarks
FROM OWOR AS T2 
     LEFT OUTER JOIN RDR1 AS T1 ON CAST(ISNULL(T1.U_Prod_Order_No, - 1) AS INT) = T2.DocNum 
     LEFT OUTER JOIN ORDR AS T0 ON T0.DocEntry = T1.DocEntry
WHERE ISNULL(T2.Status, '') IN ('P', 'R')
  AND ((T1.ShipDate <> CAST('2033-03-03' AS DATETIME) AND 
        T1.ShipDate <> CAST('2044-04-04' AS DATETIME) AND 
        T1.ShipDate <> CAST('2033-03-04' AS DATETIME))
  AND T1.ShipDate BETWEEN DATEADD(DAY, -1, cast(floor(cast(GETDATE() as float)) as datetime)) 
                      AND DATEADD(DAY, 14, cast(floor(cast(GETDATE() as float)) as datetime)))
GROUP BY T2.DueDate, T1.ShipDate, T0.DocNum
       , T0.CardName, T1.U_Prod_Order_No, T1.Quantity
       , T1.ItemCode, T1.Dscription, T2.Comments
HAVING (LOWER(T2.Comments) LIKE '%urgent%') OR
       (UPPER(T2.Comments) LIKE '%PRIORITY%')
ORDER BY [Must Despatch], [Sales Order], [Sales Partnet]

If this does not give you the expected production orders and/or sales orders, please check that the expected documents conform to the combination of your WHERE clause and HAVING clause.

Regards,

Johan

[RahF]

Hi Johan

Tried it with no luck

All production order that is linked to sales order appear with all the data, but any production order that isn't linked to a sales order are not even appearing in the query result

I don't know why?

Regards

Rahul

[Johan_H]

Hi Rahul,

Please try this:

SELECT     
T2.DueDate AS [Build Date], 
T1.ShipDate AS [Must Despatch], 
T0.DocNum AS [Sales Order], 
T0.CardName AS [Sales Partner], 
T1.U_Prod_Order_No AS [Prodn.Order], 
T1.Quantity AS Qty, 
T1.ItemCode AS [Item Code], 
T1.Dscription AS [Item Description], 
T2.Comments AS Remarks
FROM OWOR AS T2 
     LEFT OUTER JOIN RDR1 AS T1 ON CAST(ISNULL(T1.U_Prod_Order_No, - 1) AS INT) = T2.DocNum 
     LEFT OUTER JOIN ORDR AS T0 ON T0.DocEntry = T1.DocEntry
WHERE ISNULL(T2.Status, '') IN ('P', 'R')
  /*AND ((T1.ShipDate <> CAST('2033-03-03' AS DATETIME) AND 
        T1.ShipDate <> CAST('2044-04-04' AS DATETIME) AND 
        T1.ShipDate <> CAST('2033-03-04' AS DATETIME))
  AND T1.ShipDate BETWEEN DATEADD(DAY, -1, cast(floor(cast(GETDATE() as float)) as datetime)) 
                      AND DATEADD(DAY, 14, cast(floor(cast(GETDATE() as float)) as datetime)))*/
GROUP BY T2.DueDate, T1.ShipDate, T0.DocNum
       , T0.CardName, T1.U_Prod_Order_No, T1.Quantity
       , T1.ItemCode, T1.Dscription, T2.Comments
HAVING (LOWER(T2.Comments) LIKE '%urgent%') OR
       (UPPER(T2.Comments) LIKE '%PRIORITY%')
ORDER BY [Must Despatch], [Sales Order], [Sales Partner]

I bet T1.ShipDate is limiting the result set. We can test this by commenting out this part of the WHERE clause.

Regards,

Johan

[RahF]

Hi Johan

Sorry it didn't work. Getting the below error

Regards

Rahul

[Johan_H]

Hi Rahul,

That means that the field T1.U_Prod_Order_No is of the wrong data type, and has "illegal" entries in it. We can work around this by converting DocNum to NVARCHAR:

SELECT     
T2.DueDate AS [Build Date], 
T1.ShipDate AS [Must Despatch], 
T0.DocNum AS [Sales Order], 
T0.CardName AS [Sales Partner], 
T1.U_Prod_Order_No AS [Prodn.Order], 
T1.Quantity AS Qty, 
T1.ItemCode AS [Item Code], 
T1.Dscription AS [Item Description], 
T2.Comments AS Remarks
FROM OWOR AS T2 
     LEFT OUTER JOIN RDR1 AS T1 ON ISNULL(T1.U_Prod_Order_No, '-1') = CAST(T2.DocNum AS NVARCHAR) 
     LEFT OUTER JOIN ORDR AS T0 ON T0.DocEntry = T1.DocEntry
WHERE ISNULL(T2.Status, '') IN ('P', 'R')
  /*AND ((T1.ShipDate <> CAST('2033-03-03' AS DATETIME) AND 
        T1.ShipDate <> CAST('2044-04-04' AS DATETIME) AND 
        T1.ShipDate <> CAST('2033-03-04' AS DATETIME))
  AND T1.ShipDate BETWEEN DATEADD(DAY, -1, cast(floor(cast(GETDATE() as float)) as datetime)) 
                      AND DATEADD(DAY, 14, cast(floor(cast(GETDATE() as float)) as datetime)))*/
GROUP BY T2.DueDate, T1.ShipDate, T0.DocNum
       , T0.CardName, T1.U_Prod_Order_No, T1.Quantity
       , T1.ItemCode, T1.Dscription, T2.Comments
HAVING (LOWER(T2.Comments) LIKE '%urgent%') OR
       (UPPER(T2.Comments) LIKE '%PRIORITY%')
ORDER BY [Must Despatch], [Sales Order], [Sales Partner]

Regards,

Johan

[RahF]

Hi Johan

It partly worked, now in the query result I am getting the row for production order that is not linked to a sales order

But, the only data that gets populated for these production orders is the date and remarks

T2.DueDate AS [Build Date], Correct
T1.ShipDate AS [Must Despatch], Correct - Should be blank as there is no linked sales order
T0.DocNum AS [Sales Order], Correct - Should be blank as there is no linked sales order
T0.CardName AS [Sales Partner], Correct - Should be blank as there is no linked sales order
T1.U_Prod_Order_No AS [Prodn.Order], NULL
T1.Quantity AS Qty, NULL
T1.ItemCode AS [Item Code], NULL
T1.Dscription AS [Item Description], NULL
T2.Comments AS Remarks Correct

The NULL's have the data in the production order, but aren't appearing in the query results

Regards

Rahul

[Johan_H]

Hi Rahul,

The NULLs are also correct as they are taken from the sales order rows, which don't exist.

You can circumvent these with ISNULL, or use COALESCE and join in the WOR1 table to get corresponding data from the production order rows.

For example:

COALESCE(T1.Quantity, WOR1.[PlannedQty], 0) AS Qty

Regards,

Johan