Greetings,
I am thinking to further optimize a query I developed yesterday. Rather than have the UNION query have to harvest records from the real tables twice (as all records are being considered for both queries feeding the UNION) I would like to have the query create a derived table once and use the same derived table for both queries in the UNION.
Is anyone aware of a sample query that is similar to what I am envisioning?
The working query for now is as follows:
Which produces the following output:
I am thinking to further optimize a query I developed yesterday. Rather than have the UNION query have to harvest records from the real tables twice (as all records are being considered for both queries feeding the UNION) I would like to have the query create a derived table once and use the same derived table for both queries in the UNION.
Is anyone aware of a sample query that is similar to what I am envisioning?
The working query for now is as follows:
Code:
DECLARE @prodid int;
SET @prodid = 1;
SELECT [statuscode],[partnumber],[id],[title],[econumber]
FROM (SELECT 'BOM Not Complete' AS [statuscode],[p].[partnumber],[p].[id],[p].[title],
CASE
WHEN [p].[econumber] IS NULL THEN 'N/A'
ELSE [p].[econumber]
END AS [econumber]
FROM [dbo].[products] AS [prod]
INNER JOIN [dbo].[productpartlink] AS [ppl] ON [prod].[id] = [ppl].[productid]
INNER JOIN [dbo].[parts] AS [p] ON [ppl].[partid] = [p].[id]
INNER JOIN [dbo].[partsstocktype] AS [ps] ON [p].[stocktypeid] = [ps].[id]
WHERE [prod].[id] = @prodid
AND [ps].[sykassyflg] = 1
AND [p].[bomloadedflg] = 0
UNION
SELECT 'Router Not Complete' AS [statuscode],[p].[partnumber],[p].[id],[p].[title],
CASE
WHEN [p].[econumber] IS NULL THEN 'N/A'
ELSE [p].[econumber]
END AS [econumber]
FROM [dbo].[products] AS [prod]
INNER JOIN [dbo].[productpartlink] AS [ppl] ON [prod].[id] = [ppl].[productid]
INNER JOIN [dbo].[parts] AS [p] ON [ppl].[partid] = [p].[id]
INNER JOIN [dbo].[partsstocktype] AS [ps] ON [p].[stocktypeid] = [ps].[id]
WHERE [prod].[id] = @prodid
AND [ps].[sykassyflg] = 1
AND [p].[rtrcompflg] = 0)
AS [rpt]
ORDER BY [statuscode],[partnumber];
Code:
statuscode partnumber id title econumber
BOM Not Complete 0715001021 2 L BRACKET Test 21346587
BOM Not Complete 0715001134 3 BELLOWS Test N/A
BOM Not Complete 0721660035 8 SECHRIST BELLOWS N/A
Router Not Complete 0715001021 2 L BRACKET Test 21346587
Router Not Complete 0715001134 3 BELLOWS Test N/A
Router Not Complete 0716201107 7 PUMP LINK PIN (LONG) 12/09 12345