| Author |
 Topic  |
|
|
StacyOW
Yak Posting Veteran
USA
74 Posts |
 Posted - 01/23/2013 : 09:34:26
|
I am trying to do two queries and have the data end up in one table. Here is what I have so far.
USE [MT]
GO
/****** Object: StoredProcedure [dbo].[PMPartsDue] Script Date: 01/23/2013 08:10:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AllPartsDue]
(
@zWODue Integer
)
AS
BEGIN
SET NOCOUNT ON
SELECT sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor,
sparts.ComponentID, pmaint.TaskNum1, pmaint.TaskNum, pmaint.EquipmentID, pmparts.QtyNeeded
INTO partsreorder
FROM pmparts INNER JOIN
pmaint INNER JOIN
pmtask ON pmaint.TaskNum = pmtask.TaskNum ON pmparts.TaskNum = pmaint.TaskNum INNER JOIN
spartven INNER JOIN
sparts ON spartven.ComponentID = sparts.ComponentID ON pmparts.ComponentNum = sparts.ComponentID
WHERE (pmtask.NextPerfDate <= DATEADD(dd, @zWODue, DATEDIFF(dd, 0, GETDATE()))) AND (pmtask.WONum IS NULL) AND (pmaint.PMStatus = 'Active') AND
(spartven.PrimaryVendor = 'True')
UNION
SELECT sparts.ComponentID, sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.VendorID, spartven.Price,
spartven.Component
FROM sparts INNER JOIN
spartven ON sparts.ComponentID = spartven.ComponentID
WHERE (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)
SELECT *
FROM partsreorder
END
My problem is in one table I am returning more fields that in the other but all the fields are the same except for in the second query the extra fields (that are not being returned in this query) would just be blank. Is there a way to do that?
I hope that makes sense.
Thanks,
Stacy |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 01/23/2013 : 09:48:08
|
Create the partsreorder table first, and then insert into it. Also, when you do a union query, the top and bottom queries have to have the same number of fields (as you know!) and they
need to be in the same order. Just use nulls or some default value where the columns differ
INSERT INTO table (a,b,c,d,e)
Select a,b,c,d,e from table1
union
select a,null,null,d,e from table2
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
StacyOW
Yak Posting Veteran
USA
74 Posts |
Posted - 01/23/2013 : 14:11:55
|
quote:
Originally posted by jimf
Create the partsreorder table first, and then insert into it. Also, when you do a union query, the top and bottom queries have to have the same number of fields (as you know!) and they
need to be in the same order. Just use nulls or some default value where the columns differ
INSERT INTO table (a,b,c,d,e)
Select a,b,c,d,e from table1
union
select a,null,null,d,e from table2
Jim
Everyday I learn something that somebody else already knew
Thanks Jim for your reply. This is what I ended up with.
USE [MT]
GO
/****** Object: StoredProcedure [dbo].[PMPartsDue] Script Date: 01/23/2013 08:10:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AllPartsDue]
(
@zWODue Integer
)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO partsreorder (QtyonHand, PointofReorder, OrderedAmnt, PartNum, Component, Price, PrimaryVendor,
ComponentID, TaskNum1, TaskNum, EquipmentID, QtyNeeded)
SELECT sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor,
sparts.ComponentID, pmaint.TaskNum1, pmaint.TaskNum, pmaint.EquipmentID, pmparts.QtyNeeded
FROM pmparts INNER JOIN
pmaint INNER JOIN
pmtask ON pmaint.TaskNum = pmtask.TaskNum ON pmparts.TaskNum = pmaint.TaskNum INNER JOIN
spartven INNER JOIN
sparts ON spartven.ComponentID = sparts.ComponentID ON pmparts.ComponentNum = sparts.ComponentID
WHERE (pmtask.NextPerfDate <= DATEADD(dd, @zWODue, DATEDIFF(dd, 0, GETDATE()))) AND (pmtask.WONum IS NULL) AND (pmaint.PMStatus = 'Active') AND
(spartven.PrimaryVendor = 'True')
UNION
SELECT sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor,
spartven.ComponentID, NULL, NULL, NULL, NULL
FROM sparts INNER JOIN
spartven ON sparts.ComponentID = spartven.ComponentID
WHERE (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)
SELECT *
FROM partsreorder
END
It seems to be working fine except that if both queries are returning the same component# then I am getting both in the table which causes a constraint. Not sure exactly how to handle this. Is there a way when I do the 2nd query to check if any of the comp#'s are already in that partsreorder table?
Thanks for you help.
Stacy
|
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 01/24/2013 : 07:20:42
|
You can't check the 2 part to see if it's already in the table as part of a union query. You'd have to separate it out and make it it's own insert -- this shouldn't affect performance at all.
INSERT INTO partsreorder
SELECT <STUFF>
FROM <tables>
WHERE NOT EXISTS (select * from partreorder po where po.Component = <tables>.component
Jim
Everyday I learn something that somebody else already knew |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 01/25/2013 : 01:29:30
|
for that isnt it enough to add NOT EXISTS conditions based on pmparts and partsreorder table to look for components if you want to do it inline in same UNION query?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Two Queries one table
