March 6, 2012 at 2:02 pm
Sorry!
When I view the first 1000 rows of data in SQL Server, every row is exactly the same, which isn't what I expected. Same data in every field for 1000 rows...
March 6, 2012 at 2:18 pm
It is pretty hard to determine why that is. It could be one or more of the joins is returning a cartesian. There are a number of reasons that could be happening.
Maybe through a distinct after your select? That is not the best approach but it might at least shed some light on what the real problem is.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 6, 2012 at 2:23 pm
Ok, you'll have to pick apart the joins. I, of course, probably made more than 1 mistake. Copy and paste this into an empty query window. Delete the entire SELECT portion and change it simply to SELECT TOP 1000 *.
Then highlight the SELECT and the first FROM and JOIN, look at the data. Then do the same again, but add the next join and execute. Do this until you start seeing the duplicates. Then you know that join has something "funny" in it. Maybe it needs to be filtered more. Chances are, that was also the culprit that originally caused the error.
Jared
CE - Microsoft
March 7, 2012 at 11:31 am
Ok, well this is fun! :w00t:
I have gone through them all and found the culprit. While trying to debug, I have found that some of the columns that are being used in the join can have null values. Is that an issue?
Territory.TerritoryGroup1-5_ID can be NULL
SalesTransactionHdr.Territory_ID can be NULL
This part of the query is the culprit (of course the only part Jared didn't convert over for me! Probably something I did wrong I.m sure).
CREATE VIEW [dbo].[salesdtl_v1] AS
SELECT
ter.TerritoryGroup1_ID AS TerritoryGroup1_ID,
tg.GroupDescription AS Territory1Description,
ter2.TerritoryGroup2_ID AS TerritoryGroup2_ID,
tg2.GroupDescription AS Territory2Description,
ter3.TerritoryGroup3_ID AS TerritoryGroup3_ID,
tg3.GroupDescription AS Territory3Description,
ter4.TerritoryGroup4_ID AS TerritoryGroup4_ID,
tg4.GroupDescription AS Territory4Description,
ter5.TerritoryGroup5_ID AS TerritoryGroup5_ID,
tg5.GroupDescription AS Territory5Description
LEFT JOIN territory ter
On ter.territory_ID = sth.Territory_ID
LEFT JOIN territorygroup tg
On tg.territorygroup_ID = ter.territorygroup1_ID
And ter.territory_ID = sth.territory_ID
LEFT JOIN territory ter2
On ter.territory_ID = sth.Territory_ID
LEFT JOIN territorygroup tg2
On tg.territorygroup_ID = ter.territorygroup2_ID
And ter.territory_ID = sth.territory_ID
LEFT JOIN territory ter3
On ter.territory_ID = sth.Territory_ID
LEFT JOIN territorygroup tg3
On tg.territorygroup_ID = ter.territorygroup3_ID
And ter.territory_ID = sth.territory_ID
LEFT JOIN territory ter4
On ter.territory_ID = sth.Territory_ID
LEFT JOIN territorygroup tg4
On tg.territorygroup_ID = ter.territorygroup4_ID
And ter.territory_ID = sth.territory_ID
LEFT JOIN territory ter5
On ter.territory_ID = sth.Territory_ID
LEFT JOIN territorygroup tg5
On tg.territorygroup_ID = ter.territorygroup5_ID
And ter.territory_ID = sth.territory_ID
March 7, 2012 at 11:36 am
When you alias the table as something like ter5, you have to make sure that is the alias used in the join (change ON ter.colname = sth.columnname to ON ter5.columnname = sth.columnname).
Jared
CE - Microsoft
March 7, 2012 at 11:50 am
SQLKnowItAll (3/7/2012)
When you alias the table as something like ter5, you have to make sure that is the alias used in the join (change ON ter.colname = sth.columnname to ON ter5.columnname = sth.columnname).
Thank you! So glad it was an easy fix, I think I finally have it working, now to have someone test it to make sure the data is actually correct!
thanks again!
March 7, 2012 at 11:52 am
Glad to help!
Jared
CE - Microsoft
March 7, 2012 at 1:34 pm
I think I finally have it working
I'm just curious, how many rows are there, and how long does it take to run?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 8, 2012 at 11:04 am
Greg Snidow (3/7/2012)
I think I finally have it working
I'm just curious, how many rows are there, and how long does it take to run?
I am only using test data right now, so about 775 rows, takes a couple seconds.
March 8, 2012 at 11:15 am
Well of course the data wasn't correct! It wasn't returning enough rows, so I am narrowed it down and found one of the issues and I can't figure out why it isn't working.
Here is the join
INNER JOIN warehousepickinggroup wp
ON st.PickingGroup_ID = wp.PickingGroup_ID
When I add this into the query I get 300 or so less rows. I did the query outside of the large one and realized it is only pulling back the rows with data, it is ignoring the rows with NULLS. I need it to include those.
Here is what I get when I run this query, I need the first column.
SELECT WarehousePickingGroup.PickingGroupDescription, SalesTransactionDtl.Transaction_No, SalesTransactionDtl.Line_No
FROM SalesTransactionDtl INNER JOIN
WarehousePickingGroup ON SalesTransactionDtl.PickingGroup_ID = WarehousePickingGroup.PickingGroup_ID
PickingGroupDescription Trans_No Line_No
HOT ROLLED STRUCTURALS 594171
HOT ROLLED STRUCTURALS 594171
HOT ROLLED STRUCTURALS 594171
ALL STAINLESS PRODUCTS 594172
ALL STAINLESS PRODUCTS 594172
ALL STAINLESS PRODUCTS 594172
HOT ROLLED STRUCTURALS 594191
HOT ROLLED STRUCTURALS 594191
HOT ROLLED STRUCTURALS 594191
HOT ROLLED STRUCTURALS 594192
HOT ROLLED STRUCTURALS 594192
HOT ROLLED STRUCTURALS 594192
HOT ROLLED STRUCTURALS 594201
HOT ROLLED STRUCTURALS 594201
HOT ROLLED STRUCTURALS 594201
HOT ROLLED PLATE 594202
HOT ROLLED PLATE 594202
HOT ROLLED PLATE 594202
I then checked the data in the SalesTransactionDtl table and it is not pulling it back correctly. Here is that data:
There were 570 NULLs in the PickingGroupID that are listed that the first query did not pull back. Plus the first query is pulling back duplicate rows. ex, 3 rows for Trans_N0 59417 Line_No 1
Trans_No Line_No PickingGroup_ID
594161NULL
594171HR STRUCT
594172STAINLESS
594181NULL
594182NULL
594191HR STRUCT
594192HR STRUCT
594201HR STRUCT
594202HR PLATE
Any ideas?
thanks!
March 8, 2012 at 11:43 am
I am assuming that you do not know the difference between an INNER JOIN and a LEFT JOIN. Inner join only returns rows where there is a match for the joining value in both tables. A left join is saying that the table on the left of the join will return ALL rows and if it does not exist in TableB, just return nulls for the columns requested from that table.
o, in this case change the INNER JOIN to a LEFT JOIN and you should get the desired results. Also remember that the WHERE clause is evaluated AFTER the join, not before. So the joined set is assembled and then filtered as if it was a new table. This is why many people can find missing rows between 2 tables that are supposed to be exact with the following:
SELECT *
FROM sourceTable
LEFT JOIN targetTable
ON sourceTable.primaryKey = targetTable.primaryKey
WHERE targetTable.primaryKey IS NULL
So this will first take all rows in sourceTable and look for a matching primaryKey in targetTable and combine the columns. If it has a primaryKey in sourceTable that does not exist in targetTable it will fill in the targetTable columns as all NULLS with the actual data for that row from sourceTable. It will THEN filter out the rows that have data in the targetTable leaving only those rows that exist in the sourceTable.
Jared
CE - Microsoft
March 8, 2012 at 11:55 am
No, I did not know the difference. I changed it to a LEFT and now I get too many rows! It is still doing the same thing, where it is pulling back 3 records when it should be 1 for certain Trans_No. Same as the previous post.
I am beginning to really not like the previous consultant, nothing he did works correctly!!
any suggestions?
March 8, 2012 at 12:02 pm
Try this:
SELECT * FROM WarehousePickingGroup WHERE PickingGroup_ID = X and make X some pickingGroup_ID that is returning duplicates. Post the results here.
Jared
CE - Microsoft
March 8, 2012 at 12:03 pm
Since we can't see what you see, you are going to have to provide us with DDL for the tables, sample data for each of the tables, and expected results based on the sample data.
One thing I would do is narrow the scope. Only provide the columns of each table really needed, the key columns. We don't necessarilly need all the columns selected in the select statement. Sample data is just that, sample data. It doesn't need to be live data, just data that accurately represents your problem domain.
March 8, 2012 at 12:06 pm
Change this:
SELECT WarehousePickingGroup.PickingGroupDescription, SalesTransactionDtl.Transaction_No, SalesTransactionDtl.Line_No
FROM SalesTransactionDtl
INNER JOIN WarehousePickingGroup
ON SalesTransactionDtl.PickingGroup_ID = WarehousePickingGroup.PickingGroup_ID
To this:
SELECT WarehousePickingGroup.PickingGroupDescription, SalesTransactionDtl.Transaction_No, SalesTransactionDtl.Line_No
FROM SalesTransactionDtl
INNER JOIN WarehousePickingGroup
ON SalesTransactionDtl.PickingGroup_ID = WarehousePickingGroup.PickingGroup_ID
AND SalesTransactionHdr.Warehouse_No = WarehousePickingGroup.Warehouse_No
Jared
CE - Microsoft
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply