SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query to Get Record Per PK
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 01/25/2013 :  12:53:58  Show Profile  Reply with Quote
I have the following query (stored procedure)...
USE [MT]
GO
/****** Object:  StoredProcedure [dbo].[AllPartsDue]    Script Date: 01/25/2013 10:56:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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, VendorID)
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, spartven.VendorID
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 AS Expr1, NULL AS Expr2, NULL AS Expr3, NULL AS Expr4, spartven.VendorID
FROM         sparts INNER JOIN
                      spartven ON sparts.ComponentID = spartven.ComponentID
WHERE     (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)
SELECT * 
FROM partsreorder
END


This works as is but I need it to return only one record for the parent table in the query below which is linked to a child table. I have tried using the ROW_NUMBER() OVER (PARTITION BY sparts.ComponentID Order by sparts.ComponentID) as seq but it doesn't seem to like that and I am wondering if it is because I am doing three queries in this one procedure. If I split it out and just run that query by itself it works. Is there a way to do that kind of query from within this procedure?

Thanks, Stacy

code]SELECT sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor,
spartven.ComponentID, NULL AS Expr1, NULL AS Expr2, NULL AS Expr3, NULL AS Expr4, spartven.VendorID
FROM sparts INNER JOIN
spartven ON sparts.ComponentID = spartven.ComponentID
WHERE (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/25/2013 :  13:33:20  Show Profile  Reply with Quote
unless you give us some sample data from queries and explain what you mean by one record per pk. we cant have much idea on what you're talking on. So please post some data to illustrate your scenario.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 01/25/2013 :  14:53:12  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

unless you give us some sample data from queries and explain what you mean by one record per pk. we cant have much idea on what you're talking on. So please post some data to illustrate your scenario.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Sorry I think I was thinking and typing at the same time and not getting my train of thought straight. I have the one procedure (shown above) that is doing two different queries and then putting the results in one table all together.
My problem is for that second query:
SELECT     sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, 
                      spartven.ComponentID, NULL AS Expr1, NULL AS Expr2, NULL AS Expr3, NULL AS Expr4, spartven.VendorID
FROM         sparts INNER JOIN
                      spartven ON sparts.ComponentID = spartven.ComponentID
WHERE     (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)

it is returning multiple records because the sparts is the parent table and spartven is the child. I was trying to do something like this:
SELECT *
FROM
(
SELECT    sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, 
                      spartven.ComponentID, NULL AS Expr1, NULL AS Expr2, compequip.EquipmentID, NULL AS Expr4, spartven.VendorID, ROW_NUMBER() OVER (PARTITION BY sparts.ComponentID Order by sparts.ComponentID) as Seq
FROM         sparts INNER JOIN
                      spartven ON sparts.ComponentID = spartven.ComponentID INNER JOIN
                      compequip ON sparts.ComponentID = compequip.ComponentID
WHERE     (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)
)P
WHERE P.Seq = 1

for that query but when I put this code into my stored procedure I get an error:
Msg 205, Level 16, State 1, Procedure AllPartsDue, Line 8
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Anyway I hope that makes sense now. I'm not sure how I would send you sample data.
Thanks for your help.
Stacy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/26/2013 :  00:23:41  Show Profile  Reply with Quote
you can do that in UNION itself


USE [MT]
GO
/****** Object:  StoredProcedure [dbo].[AllPartsDue]    Script Date: 01/25/2013 10:56:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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, VendorID)
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, spartven.VendorID
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 QtyonHand, PointofReorder, OrderedAmnt, PartNum, Component, Price, PrimaryVendor, 
                      ComponentID, Expr1, Expr2, EquipmentID, Expr4, VendorID
FROM
(
SELECT    sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, 
                      spartven.ComponentID, NULL AS Expr1, NULL AS Expr2, compequip.EquipmentID, NULL AS Expr4, spartven.VendorID, ROW_NUMBER() OVER (PARTITION BY sparts.ComponentID Order by sparts.ComponentID) as Seq
FROM         sparts INNER JOIN
                      spartven ON sparts.ComponentID = spartven.ComponentID INNER JOIN
                      compequip ON sparts.ComponentID = compequip.ComponentID
WHERE     (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)
)P
WHERE P.Seq = 1

SELECT * 
FROM partsreorder
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 01/30/2013 :  11:50:32  Show Profile  Reply with Quote
Wow I swear that is what I tried something must have been wrong somewhere. Because I originally had it using the Select * and when I got the error I tried changing it to list all the field names but I was still getting that error.

Well anyway atleast I was on the right track. Thanks for your help.
Stacy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/30/2013 :  12:11:33  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 01/31/2013 :  12:07:31  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




OK still have a problem. The problem is on the insert I believe instead of on the select.

If the first table inserts a record into partsreorder table and then the second query tries to select a record with the same Comp# (PK) I am getting the following error.

Msg 2627, Level 14, State 1, Procedure AllPartsDue, Line 8
Violation of PRIMARY KEY constraint 'PK_partsreorder'. Cannot insert duplicate key in object 'dbo.partsreorder'.
The statement has been terminated.

(1 row(s) affected)

The second query just does a select but it is also inserting?
Thanks again,
Stacy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/31/2013 :  12:17:55  Show Profile  Reply with Quote
its not the second query. its second query after UNION which is culprit. Make sure you add a logic to select only those from second query which are not present in first query using NOT EXISTS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 01/31/2013 :  13:13:16  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

its not the second query. its second query after UNION which is culprit. Make sure you add a logic to select only those from second query which are not present in first query using NOT EXISTS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




OK read about NOT EXISTS haven't used that yet. Anyway this is where I'm going with the second query:
SELECT  sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, 
                      spartven.ComponentID, NULL AS Expr1, NULL AS Expr2, compequip.EquipmentID, NULL AS Expr4, spartven.VendorID, equipment.DepartmentID
FROM         sparts INNER JOIN
                      spartven ON sparts.ComponentID = spartven.ComponentID INNER JOIN
                      compequip ON sparts.ComponentID = compequip.ComponentID INNER JOIN
                      equipment ON compequip.EquipmentID = equipment.EquipmentID 
WHERE NOT EXISTS (SELECT * FROM sparts JOIN partsreorder ON sparts.ComponentID = partsreorder.ComponentID WHERE sparts.ComponentID = partsreorder.ComponentID ) AND (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)


Of course this doesn't work but I can't seem to find any NOT EXISTS examples with as many joins as I have. I was thinking I could do the WHERE NOT EXISTS and use the partsreorder table because the first query INSERTS INTO that table. I think I'm close, well I hope I'm close.

Stacy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/31/2013 :  13:22:56  Show Profile  Reply with Quote
you require only this


SELECT  sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, 
                      spartven.ComponentID, NULL AS Expr1, NULL AS Expr2, compequip.EquipmentID, NULL AS Expr4, spartven.VendorID, equipment.DepartmentID
FROM         sparts INNER JOIN
                      spartven ON sparts.ComponentID = spartven.ComponentID INNER JOIN
                      compequip ON sparts.ComponentID = compequip.ComponentID INNER JOIN
                      equipment ON compequip.EquipmentID = equipment.EquipmentID 
WHERE NOT EXISTS (SELECT * FROM partsreorder WHERE sparts.ComponentID = partsreorder.ComponentID )


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 01/31/2013 :  13:53:57  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

you require only this


SELECT  sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, 
                      spartven.ComponentID, NULL AS Expr1, NULL AS Expr2, compequip.EquipmentID, NULL AS Expr4, spartven.VendorID, equipment.DepartmentID
FROM         sparts INNER JOIN
                      spartven ON sparts.ComponentID = spartven.ComponentID INNER JOIN
                      compequip ON sparts.ComponentID = compequip.ComponentID INNER JOIN
                      equipment ON compequip.EquipmentID = equipment.EquipmentID 
WHERE NOT EXISTS (SELECT * FROM partsreorder WHERE sparts.ComponentID = partsreorder.ComponentID )


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




OK tried that but I still need the (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder) so this is what I have now.
USE [MT]
GO
/****** Object:  StoredProcedure [dbo].[AllPartsDue]    Script Date: 01/31/2013 12:42:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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, VendorID, DepartmentID)
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, spartven.VendorID, equipment.DepartmentID
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 INNER JOIN
                      equipment ON pmaint.EquipmentID = equipment.EquipmentID
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     QtyonHand, PointofReorder, OrderedAmnt, PartNum, Component, Price, PrimaryVendor, 
                      ComponentID, NULL AS Expr1, NULL AS Expr2, EquipmentID, NULL AS Expr4, VendorID, DepartmentID
FROM
(
SELECT  sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, 
                      spartven.ComponentID, NULL AS Expr1, NULL AS Expr2, compequip.EquipmentID, NULL AS Expr4, spartven.VendorID, equipment.DepartmentID, ROW_NUMBER() OVER (PARTITION BY sparts.ComponentID Order By Sparts.ComponentID) as Seq
FROM         sparts INNER JOIN
                      spartven ON sparts.ComponentID = spartven.ComponentID INNER JOIN
                      compequip ON sparts.ComponentID = compequip.ComponentID INNER JOIN
                      equipment ON compequip.EquipmentID = equipment.EquipmentID
WHERE NOT EXISTS (SELECT * FROM partsreorder WHERE sparts.ComponentID = partsreorder.ComponentID ) AND (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)
)P
WHERE P.Seq = 1
SELECT * 
FROM partsreorder
END


And I am getting the following error executing stored procedure right in SQL.
Msg 2627, Level 14, State 1, Procedure AllPartsDue, Line 8
Violation of PRIMARY KEY constraint 'PK_partsreorder'. Cannot insert duplicate key in object 'dbo.partsreorder'.
The statement has been terminated.

(1 row(s) affected)

I have ran that second query by itself and if there is a record in the partsreorder table it doesn't select that record again. So I'm not sure where the constraint is coming from. It runs the first query and puts the records in the partsreorder table right? Then it runs the second query using the UNION and selects the rest of the records minus any that are already in the partsreorder table? Hmmm...
Stacy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/31/2013 :  13:57:26  Show Profile  Reply with Quote
then it means first query itself is giving duplicates for same ComponentID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 01/31/2013 :  14:02:10  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

then it means first query itself is giving duplicates for same ComponentID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Nope just ran it and it first query only returns one record.

When we do the second query with the union does it reselect the record in the partsreorder table again? Is it possible that it is trying to reselect that record and then reinsert it into the table again?

What if I tried to just do an insert query for the second and kinda manually get all the records into the table?

Stacy

Edited by - StacyOW on 01/31/2013 14:05:12
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/01/2013 :  02:39:37  Show Profile  Reply with Quote
nope. UNION will apply distinct over resultset after combining both queries. so provided you've NOT EXISTS condition, it wont select record with same ComponentID again

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 02/01/2013 :  10:11:29  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

nope. UNION will apply distinct over resultset after combining both queries. so provided you've NOT EXISTS condition, it wont select record with same ComponentID again

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





I don't get it. I changed the query to the following (both inserting records into partsreorder and I get no error and all the records in the table are correct. I'm stumped. I guess I don't understand the nitty gritty about the union query to understand where the problem is happening. How can it work like the query below but not when I use the UNION?

USE [MT]
GO
/****** Object:  StoredProcedure [dbo].[AllPartsDue]    Script Date: 02/01/2013 09:09:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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, VendorID, DepartmentID)
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, spartven.VendorID, equipment.DepartmentID
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 INNER JOIN
                      equipment ON pmaint.EquipmentID = equipment.EquipmentID
WHERE     (pmtask.NextPerfDate <= DATEADD(dd, @zWODue, DATEDIFF(dd, 0, GETDATE()))) AND (pmtask.WONum IS NULL) AND (pmaint.PMStatus = 'Active') AND 
                      (spartven.PrimaryVendor = 'True')
                      
INSERT INTO partsreorder (QtyonHand, PointofReorder, OrderedAmnt, PartNum, Component, Price, PrimaryVendor, 
                      ComponentID, TaskNum1, TaskNum, EquipmentID, QtyNeeded, VendorID, DepartmentID)
SELECT     QtyonHand, PointofReorder, OrderedAmnt, PartNum, Component, Price, PrimaryVendor, 
                      ComponentID, NULL AS Expr1, NULL AS Expr2, EquipmentID, NULL AS Expr4, VendorID, DepartmentID
FROM
(
SELECT  sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, 
                      spartven.ComponentID, NULL AS Expr1, NULL AS Expr2, compequip.EquipmentID, NULL AS Expr4, spartven.VendorID, equipment.DepartmentID, ROW_NUMBER() OVER (PARTITION BY sparts.ComponentID Order By Sparts.ComponentID) as Seq
FROM         sparts INNER JOIN
                      spartven ON sparts.ComponentID = spartven.ComponentID INNER JOIN
                      compequip ON sparts.ComponentID = compequip.ComponentID INNER JOIN
                      equipment ON compequip.EquipmentID = equipment.EquipmentID
WHERE NOT EXISTS (SELECT * FROM partsreorder WHERE sparts.ComponentID = partsreorder.ComponentID ) AND (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)
)P
WHERE P.Seq = 1
SELECT * 
FROM partsreorder
END


Thanks,
Stacy
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 02/01/2013 :  10:25:32  Show Profile  Reply with Quote
What a UNION will do is to remove all duplicates. Two rows are considered duplicates if they are exactly identical in every column. So even if one column is different, you will get both rows in the result set.

Probably the easiest way to debug this is to first comment out the INSERT portion and run the stored procedure. That will show you how many rows are returned from the UNIONed query. Examine that to see if it will cause a PK violation when inserted into the table.
-- for testing, comment this out
--INSERT INTO partsreorder (QtyonHand, PointofReorder, OrderedAmnt, PartNum, Component, Price, PrimaryVendor, 
--                      ComponentID, TaskNum1, TaskNum, EquipmentID, QtyNeeded, VendorID, DepartmentID)
Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 02/01/2013 :  12:01:03  Show Profile  Reply with Quote
quote:
Originally posted by James K

What a UNION will do is to remove all duplicates. Two rows are considered duplicates if they are exactly identical in every column. So even if one column is different, you will get both rows in the result set.

Probably the easiest way to debug this is to first comment out the INSERT portion and run the stored procedure. That will show you how many rows are returned from the UNIONed query. Examine that to see if it will cause a PK violation when inserted into the table.
-- for testing, comment this out
--INSERT INTO partsreorder (QtyonHand, PointofReorder, OrderedAmnt, PartNum, Component, Price, PrimaryVendor, 
--                      ComponentID, TaskNum1, TaskNum, EquipmentID, QtyNeeded, VendorID, DepartmentID)



I know what the problem is then. One of the fields is different from the first query to the second. In the first query it returns the field Task# - in the second query they do not return any data in that field - just NULL. So I guess that wouldn't have worked for me. I was thinking the UNION would use the PK to see if the records where the same - that was kinda what I was looking to do - I wouldn't want it to return any of the same Component#'s (that were inserted in the first query) when doing the second query.
Thanks for your reply.
Stacy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/01/2013 :  12:20:38  Show Profile  Reply with Quote
nope..thats the point
UNION applies distinct over entire column value combination not PK alone.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 02/01/2013 :  15:31:26  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

nope..thats the point
UNION applies distinct over entire column value combination not PK alone.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




OK thanks - understood. Problem solved anyway!
Thanks for all your help your guys!
Stacy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/02/2013 :  01:48:45  Show Profile  Reply with Quote
no issues

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000