Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 view created wrong?

Author  Topic 

manared
Starting Member

10 Posts
Posted - 2009-04-09 : 15:13:59
I have a view that has been created from 2 tables and 2 other views. There are times where I use a sql query to get into this view (vDWSOItems) and some of the line items are duplicated. I'm not sure why this is happening. I'm wondering if the view was created incorrectly where something is grabbing these items twice? Here's the sql query that creates the view:

SELECT     dbo.vDWSO.SONo, dbo.vDWSO.Company, dbo.vDWSO.CustPO, dbo.vDWSO.CustNo, dbo.vDWSO.DueDate, dbo.vDWSO.OrderDate, 
                      dbo.vDWSO.fstatus, dbo.vDWSO.Qty, dbo.vDWSO.PartNo, dbo.vDWSO.UnitPrice, dbo.vDWSO.Price, dbo.vDWSO.finumber, dbo.vDWSO.SOTYPE, 
                      dbo.vDWSO.INV, dbo.soitem.fdesc AS PartDesc, dbo.shmast.fshipno AS Shipper, dbo.vDWJONotCanceled.fjobno AS JO, dbo.vDWSO.fdelivery
FROM         dbo.vDWSO LEFT OUTER JOIN
                      dbo.vDWJONotCanceled ON dbo.vDWSO.SONo = dbo.vDWJONotCanceled.fsono AND 
                      dbo.vDWSO.PartNo = dbo.vDWJONotCanceled.fpartno LEFT OUTER JOIN
                      dbo.shmast ON dbo.vDWSO.SONo = dbo.shmast.fcsono LEFT OUTER JOIN
                      dbo.soitem ON dbo.vDWSO.SONo = dbo.soitem.fsono AND dbo.vDWSO.finumber = dbo.soitem.finumber


Some items are coming in just fine, whereas others are duplicating. is there an error in the way this view was created that's causing this problem?

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-04-09 : 15:15:02
It's hard to help with the information that you have provided. We aren't familiar with your environment, so we'll need you to show us sample data of what you mean.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

manared
Starting Member

10 Posts
Posted - 2009-04-09 : 15:28:51
I'm not exactly sure what you're asking, but here's a sql statement that I'm sending:

SELECT * FROM vDWSOItems LEFT OUTER JOIN vDWShip ON vDWSOItems.Shipper = vDWShip.fshipno AND vDWSOItems.SONO = vDWShip.fsono WHERE (vDWSOItems.DueDate <= '3/7/2009' AND vDWSOItems.DueDate >= '3/5/2009') AND (vDWShip.fshipdate > vDWSOItems.DueDate OR vDWShip.fshipdate is null)


These lines are what I'm getting in return:

      SONo	Company	CustPO	DueDate	Qty	PartNo	UnitPrice	Price		PartDesc	Shipper	JO	fshipno	fshipdate	fshipno
25567	EDUCATIONAL FURNITURE & EQUIPEMENT 	2203	6-Mar-2009	1	520057	0	0	3	APRON ASSY,TABLE,OAK,PLAIN,36X60   	33602	23338-0000	33602	30-Mar-2009	25567
25567	EDUCATIONAL FURNITURE & EQUIPEMENT 	2203	6-Mar-2009	1	520057	0	0	3	APRON ASSY,TABLE,OAK,PLAIN,36X60   	33602	23338-0000	33602	30-Mar-2009	25567
25567	EDUCATIONAL FURNITURE & EQUIPEMENT 	2203	6-Mar-2009	16	P7224K30N-RS-MOD         	578.22	9251.52	1	TABLE,PLAIN APRON,1"PHENOLIC TOP,36x60x30H 	33602	22787-0000	33602	30-Mar-2009	25567
25567	EDUCATIONAL FURNITURE & EQUIPEMENT 	2203	6-Mar-2009	16	P7224K30N-RS-MOD         	578.22	9251.52	1	TABLE,PLAIN APRON,1"PHENOLIC TOP,36x60x30H 	33602	22787-0000	33602	30-Mar-2009	25567
25567	EDUCATIONAL FURNITURE & EQUIPEMENT 	2203	6-Mar-2009	16	100604	63.45	1015.2	2	ROD SET W/UPRIGHTS & CROSSBAR            QUOTE 11590	33602	22788-0000	33602	30-Mar-2009	25567
25567	EDUCATIONAL FURNITURE & EQUIPEMENT 	2203	6-Mar-2009	16	100604	63.45	1015.2	2	ROD SET W/UPRIGHTS & CROSSBAR            QUOTE 11590	33602	22788-0000	33602	30-Mar-2009	25567


There are 6 lines, but there should only be 3. is this what you're looking for?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-04-10 : 07:01:03
is relation between vDWSOItems & vDWShip 1 to 1? also are joining on correct criteria? some sample data from views might help
Go to Top of Page

manared
Starting Member

10 Posts
Posted - 2009-04-13 : 09:22:40
Here is sample data of vDWShip


fshipno	fshipdate	fpartno	fdesc	forderqty	fshipqty	funetprice	fnetprice	fshipvia	fmtrckno	fsono	fcustno	fcompany	fcustpono	SOTYPE
33137	3/2/2009 0:00	100604                   	ROD SET W/UPRIGHTS & CROSSBAR      	16	16	63.45	1015.2	ROADWAY                                                                         	338-090132-2	25567	694	EDUCATIONAL FURNITURE & EQUIPEMENT	2203                	Catalog             
			QUOTE 11590											
33137	3/2/2009 0:00	P7224K30N-RS-MOD         	TABLE,PLAIN APRON,1"PHENOLIC TOP,36x60x30H WITH 2 ROD SOCKETS INCLUDING 2-206505 ROD SOCKET ASSEMBLIES, MODIFIED TO HAVE ELECTRICAL PEDESTAL WITH 4 OUTLETS(2 ON EACH SIDE) TO INCLUDE 1-100754 PEDISTAL BOX, 2-100034	16	16	578.22	9251.52	ROADWAY                                                                         	338-090132-2	25567	694	EDUCATIONAL FURNITURE & EQUIPEMENT	2203                	Catalog             
			GFI OUTLETS, AND 2-100779 COVER PLATES.											
			PLACEMENT OF ROD SOCKETS AND ELECTICAL PEDESTAL TO BE PROVIDED WHEN ORDER IS PLACED.											
33602	3/30/2009 0:00	520057                   	APRON ASSY,TABLE,OAK,PLAIN,36X60   	1	1	0	0	UPS-GROUND                                                                      	1Z5779250352680758	25567	694	EDUCATIONAL FURNITURE & EQUIPEMENT	2203                	Catalog             



Sample of vDWSOItems


CustPO	CustNo	DueDate	OrderDate	fstatus	Qty	PartNo	UnitPrice	Price	SpecialNeed	finumber	SOTYPE	INV	PartDesc	Shipper	JO	fdelivery
2203                	694	3/6/2009	1/29/2009	Closed              	16	P7224K30N-RS-MOD         	578.22	9251.52	FALSE	 1	Catalog             	0000027790          	TABLE,PLAIN APRON,1"PHENOLIC TOP,36x60x30H WITH 2 ROD SOCKETS INCLUDING 2-206505 ROD SOCKET ASSEMBLIES	33137	22787-0000	3/6/2009
2203                	694	3/6/2009	1/29/2009	Closed              	16	P7224K30N-RS-MOD         	578.22	9251.52	FALSE	 1	Catalog             	0000027790          	TABLE,PLAIN APRON,1"PHENOLIC TOP,36x60x30H WITH 2 ROD SOCKETS INCLUDING 2-206505 ROD SOCKET ASSEMBLIES	33602	22787-0000	3/6/2009
2203                	694	3/6/2009	1/29/2009	Closed              	16	P7224K30N-RS-MOD         	578.22	9251.52	FALSE	 1	Catalog             	0000028138          	TABLE,PLAIN APRON,1"PHENOLIC TOP,36x60x30H WITH 2 ROD SOCKETS INCLUDING 2-206505 ROD SOCKET ASSEMBLIES	33137	22787-0000	3/6/2009
2203                	694	3/6/2009	1/29/2009	Closed              	16	P7224K30N-RS-MOD         	578.22	9251.52	FALSE	 1	Catalog             	0000028138          	TABLE,PLAIN APRON,1"PHENOLIC TOP,36x60x30H WITH 2 ROD SOCKETS INCLUDING 2-206505 ROD SOCKET ASSEMBLIES	33602	22787-0000	3/6/2009
2203                	694	3/6/2009	1/29/2009	Closed         	16	100604                   	63.45	1015.2	FALSE	 2	Catalog             	0000027790          	ROD SET W/UPRIGHTS & CROSSBAR      	33137	22788-0000	3/6/2009
2203                	694	3/6/2009	1/29/2009	Closed              	16	100604                   	63.45	1015.2	FALSE	 2	Catalog             	0000027790          	ROD SET W/UPRIGHTS & CROSSBAR      	33602	22788-0000	3/6/2009
2203                	694	3/6/2009	1/29/2009	Closed              	16	100604                   	63.45	1015.2	FALSE	 2	Catalog             	0000028138          	ROD SET W/UPRIGHTS & CROSSBAR      	33137	22788-0000	3/6/2009
2203                	694	3/6/2009	1/29/2009	Closed              	16	100604                   	63.45	1015.2	FALSE	 2	Catalog             	0000028138          	ROD SET W/UPRIGHTS & CROSSBAR      	33602	22788-0000	3/6/2009
2203                	694	3/6/2009	1/29/2009	Closed              	1	520057                   	0	0	FALSE	 3	Catalog             	0000027790          	APRON ASSY,TABLE,OAK,PLAIN,36X60   	33137	23338-0000	3/30/2009
2203                	694	3/6/2009	1/29/2009	Closed              	1	520057                   	0	0	FALSE	 3	Catalog             	0000027790          	APRON ASSY,TABLE,OAK,PLAIN,36X60   	33602	23338-0000	3/30/2009
2203                	694	3/6/2009	1/29/2009	Closed              	1	520057                   	0	0	FALSE	 3	Catalog             	0000028138          	APRON ASSY,TABLE,OAK,PLAIN,36X60   	33137	23338-0000	3/30/2009
2203                	694	3/6/2009	1/29/2009	Closed              	1	520057                   	0	0	FALSE	 3	Catalog             	0000028138          	APRON ASSY,TABLE,OAK,PLAIN,36X60   	33602	23338-0000	3/30/2009


I know that the sample data for vDWSOItems is duplicated. So this view alone might be the whole problem. This view is created like this:

SELECT     dbo.vDWSO.SONo, dbo.vDWSO.Company, dbo.vDWSO.CustPO, dbo.vDWSO.CustNo, dbo.vDWSO.DueDate, dbo.vDWSO.OrderDate, 
                      dbo.vDWSO.fstatus, dbo.vDWSO.Qty, dbo.vDWSO.PartNo, dbo.vDWSO.UnitPrice, dbo.vDWSO.Price, dbo.vDWSO.SpecialNeed, dbo.vDWSO.finumber, 
                      dbo.vDWSO.SOTYPE, dbo.vDWSO.INV, dbo.soitem.fdesc AS PartDesc, dbo.shmast.fshipno AS Shipper, dbo.vDWJONotCanceled.fjobno AS JO, 
                      dbo.vDWSO.fdelivery
FROM         dbo.vDWSO LEFT OUTER JOIN
                      dbo.vDWJONotCanceled ON dbo.vDWSO.SONo = dbo.vDWJONotCanceled.fsono AND 
                      dbo.vDWSO.PartNo = dbo.vDWJONotCanceled.fpartno LEFT OUTER JOIN
                      dbo.shmast ON dbo.vDWSO.SONo = dbo.shmast.fcsono LEFT OUTER JOIN
                      dbo.soitem ON dbo.vDWSO.finumber = dbo.soitem.finumber AND dbo.vDWSO.SONo = dbo.soitem.fsono
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site