April 28, 2016 at 12:18 am
Hello Guys,
Since my SQL Developer was on leave so I tried my hands to write some queries. I wrote the following Query,
[Code="sql"]SELECT POC.[Prod_ Order No_],CASE WHEN POC.Status = 1 Then 'Planned' When POC.Status = 2 Then 'Firm Planned' When POC.Status = 3 then 'Released' END as Status,
(Select POL.[Item No_] From [XXX$Prod_ Order Line] AS POL WHERE (POL.[Prod_ Order No_] = POC.[Prod_ Order No_]) AND (POL.Status = POC.Status)) AS 'Prod. Order Item No.',
(Select POL.Quantity From [XXX$Prod_ Order Line] AS POL WHERE (POL.[Prod_ Order No_] = POC.[Prod_ Order No_]) AND (POL.Status = POC.Status)) AS 'Prod. Order Quantity',
(Select POL.[Due Date] From [XXX$Prod_ Order Line] AS POL WHERE (POL.[Prod_ Order No_] = POC.[Prod_ Order No_]) AND (POL.Status = POC.Status)) AS 'Prod. Order Due Date',
POC.[Item No_],POC.Description,POC.[Manufacturer Code],POC.[Mfg_ Part No_],
(Select Item.Buyer FROM [XXX$Item] As Item WHere Item.[No_] = POC.[Item No_]) As Buyer,
(Select Item.[Planner_Engr Code] FROM [XXX$Item] As Item WHere Item.[No_] = POC.[Item No_]) As 'Planner',
(Select Item.[Customer No_] FROM [XXX$Item] As Item WHere Item.[No_] = POC.[Item No_]) As 'Customer No_',
(Select Item.[Vendor No_] FROM [XXX$Item] As Item WHere Item.[No_] = POC.[Item No_]) As Supplier,
(Select Item.[Lead Time Calculation] FROM [XXX$Item] As Item WHere Item.[No_] = POC.[Item No_]) As LeadTime,
(Select Item.[Unit Cost] FROM [XXX$Item] As Item WHere Item.[No_] = POC.[Item No_]) As 'Unit Cost',
(Select CASE WHEN Item.[Replenishment System] = 0 Then 'Purchase' ELSE 'Prod. Order' END FROM [XXX$Item] As Item WHere Item.[No_] = POC.[Item No_]) As 'Replenishment System',
POC.[Remaining Qty_ (Base)],CONVERT(VARCHAR(10),POC.[Due Date],3) As 'Due Date',
(Select SUM(ILE.[Remaining Quantity]) FROM [XXX$Item Ledger Entry] AS ILE WHERE (ILE.[Item No_ ]= POC.[Item No_])) AS Inventory,
(Select SUM(CL.[Remaining Qty_ (Base)]) FROM [XXX$Prod_ Order Component] AS CL WHERE (CL.[Item No_] = POC.[Item No_]) AND (CL.Status IN(1,2,3))) AS 'Total Rem. Qty.',
(Select SUM(POL.[Outstanding Qty_ (Base)]) FROM [XXX$Purchase Line] AS POL WHERE POL.[No_] = POC.[Item No_]) AS 'PO Qty.'
FROM [XXX$Prod_ Order Component] AS POC
WHERE POC.Status IN(1,2,3) AND (POC.[Prod_ Order No_] <> '') AND (POC.[Item No_] <> '')
Order By POC.[Item No_],POC.[Location Code],POC.Status
[/Code]
I know its very bad way to do that. But how can i combine 2 or 3 different tables to get the required Data.
Appreciate your help on this.
April 28, 2016 at 12:41 am
Its hard to read your code from all those selects, so I can't work out all the sums, converts, cases etc.... but below I think I've joined it for you correctly on what you asked....
its an Inner join for all ... if you need a left join than change it accordingly
SELECT /*enter the specific columns you want*/
poc.* ,
pol.*,
Item.*,
ILE.*,
pl.*
FROM
[XXX$Prod_ Order Component] poc
INNER JOIN [XXX$Prod_ Order Line] pol
ON POL.[Prod_ Order No_] = POC.[Prod_ Order No_]
INNER JOIN [XXX$Item] Item
ON Item.[No_] = POC.[Item No_]
INNER JOIN [XXX$Item Ledger Entry] ILE
ON ILE.[Item No_ ]= POC.[Item No_]
INNER JOIN [XXX$Purchase Line] pl
ON pl.[no] = POC.[Item No_]
/*
WHERE
-- whatever conditions you require
*/
The above will just give you a start...
April 28, 2016 at 12:44 am
site below is very basic and simple to understand... have a read it will hopefully guide you further.. any questions feel free to ask
April 28, 2016 at 2:45 am
I've reformatted the code so that it's readable:
SELECT POC.[Prod_ Order No_],
CASE WHEN POC.Status = 1 THEN 'Planned'
WHEN POC.Status = 2 THEN 'Firm Planned'
WHEN POC.Status = 3 THEN 'Released'
END AS Status,
(SELECT POL.[Item No_]
FROM [XXX$Prod_ Order Line] AS POL
WHERE (POL.[Prod_ Order No_] = POC.[Prod_ Order No_])
AND (POL.Status = POC.Status)
) AS 'Prod. Order Item No.',
(SELECT POL.Quantity
FROM [XXX$Prod_ Order Line] AS POL
WHERE (POL.[Prod_ Order No_] = POC.[Prod_ Order No_])
AND (POL.Status = POC.Status)
) AS 'Prod. Order Quantity',
(SELECT POL.[Due Date]
FROM [XXX$Prod_ Order Line] AS POL
WHERE (POL.[Prod_ Order No_] = POC.[Prod_ Order No_])
AND (POL.Status = POC.Status)
) AS 'Prod. Order Due Date',
POC.[Item No_],
POC.Description,
POC.[Manufacturer Code],
POC.[Mfg_ Part No_],
(SELECT Item.Buyer
FROM [XXX$Item] AS Item
WHERE Item.No_ = POC.[Item No_]
) AS Buyer,
(SELECT Item.[Planner_Engr Code]
FROM [XXX$Item] AS Item
WHERE Item.No_ = POC.[Item No_]
) AS 'Planner',
(SELECT Item.[Customer No_]
FROM [XXX$Item] AS Item
WHERE Item.No_ = POC.[Item No_]
) AS 'Customer No_',
(SELECT Item.[Vendor No_]
FROM [XXX$Item] AS Item
WHERE Item.No_ = POC.[Item No_]
) AS Supplier,
(SELECT Item.[Lead Time Calculation]
FROM [XXX$Item] AS Item
WHERE Item.No_ = POC.[Item No_]
) AS LeadTime,
(SELECT Item.[Unit Cost]
FROM [XXX$Item] AS Item
WHERE Item.No_ = POC.[Item No_]
) AS 'Unit Cost',
(SELECT CASE WHEN Item.[Replenishment System] = 0 THEN 'Purchase'
ELSE 'Prod. Order'
END
FROM [XXX$Item] AS Item
WHERE Item.No_ = POC.[Item No_]
) AS 'Replenishment System',
POC.[Remaining Qty_ (Base)],
CONVERT(VARCHAR(10), POC.[Due Date], 3) AS 'Due Date',
(SELECT SUM(ILE.[Remaining Quantity])
FROM [XXX$Item Ledger Entry] AS ILE
WHERE (ILE.[Item No_ ] = POC.[Item No_])
) AS Inventory,
(SELECT SUM(CL.[Remaining Qty_ (Base)])
FROM [XXX$Prod_ Order Component] AS CL
WHERE (CL.[Item No_] = POC.[Item No_])
AND (CL.Status IN (1, 2, 3))
) AS 'Total Rem. Qty.',
(SELECT SUM(POL.[Outstanding Qty_ (Base)])
FROM [XXX$Purchase Line] AS POL
WHERE POL.No_ = POC.[Item No_]
) AS 'PO Qty.'
FROM [XXX$Prod_ Order Component] AS POC
WHERE POC.Status IN (1, 2, 3)
AND (POC.[Prod_ Order No_] <> '')
AND (POC.[Item No_] <> '')
ORDER BY POC.[Item No_],
POC.[Location Code],
POC.Status;
You really need to learn how to use the JOIN operation. That's the key to doing this. I took the first one of your sub-selects and modified the code as follows:
SELECT POC.[Prod_ Order No_],
CASE WHEN POC.Status = 1 THEN 'Planned'
WHEN POC.Status = 2 THEN 'Firm Planned'
WHEN POC.Status = 3 THEN 'Released'
END AS Status,
POL.[Item No_] AS 'Prod. Order Item No.'
FROM [XXX$Prod_ Order Component] AS POC
JOIN [XXX$Prod_ Order Line] AS POL
ON (POL.[Prod_ Order No_] = POC.[Prod_ Order No_])
AND (POL.Status = POC.Status)
WHERE POC.Status IN (1, 2, 3)
AND (POC.[Prod_ Order No_] <> '')
AND (POC.[Item No_] <> '')
ORDER BY POC.[Item No_],
POC.[Location Code],
POC.Status;
It's the JOIN...ON operations you need to focus on here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2016 at 7:39 am
I would actually leave the last 3 subqueries. I'd probably move them to the from using the APPLY operator. The reason is that those seem to be unrelated quantities which when joined would give incorrect results.
This is an untested extract
SELECT POC.[Prod_ Order No_],
CASE WHEN POC.Status = 1 Then 'Planned'
When POC.Status = 2 Then 'Firm Planned'
When POC.Status = 3 then 'Released' END AS Status,
ILE.Inventory,
CL.[Total Rem. Qty.]
FROM [XXX$Prod_ Order Component] AS POC
OUTER APPLY (Select SUM(ILE.[Remaining Quantity])
FROM [XXX$Item Ledger Entry] AS ILE
WHERE ILE.[Item No_ ] = POC.[Item No_]) AS ILE(Inventory)
OUTER APPLY (Select SUM(CL.[Remaining Qty_ (Base)])
FROM [XXX$Prod_ Order Component] AS CL
WHERE CL.[Item No_] = POC.[Item No_]
AND CL.Status IN(1,2,3)) AS CL([Total Rem. Qty.])
WHERE POC.Status IN(1,2,3)
AND POC.[Prod_ Order No_] <> ''
AND POC.[Item No_] <> ''
Order By POC.[Item No_],POC.[Location Code],POC.Status
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply