Getting wrong row counts - Frustrated :(

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 2000 Forums

Transact-SQL (2000)

Getting wrong row counts - Frustrated :(

Author

Topic

Joozh
Posting Yak Master

145 Posts

Posted - 2004-04-21 : 00:49:42

Hi,

...giving a very 'summarized' scenario of the problem I have trying to solve all day (make it 2 days now).

Below are the relevant DDLs... I am not listing the DDLs of my other tables:

CREATE TABLE [SalesFACT] (
[UniqueProdCode] [varchar] (10),
[TransDate] [varchar] (10),
[SaleAmt] [float],
[CustCode] [varchar] (10)
. . .
)

I populate the above table via a DTS and have checked and have verified that correct data is coming in... I also have a product master table; for business reasons we can have the same product created with different ProductCodes though the rest of the Product details are EXACTLY the same. We have covered this using a field named 'UniqueProdCode'.

CREATE TABLE ProdMaster(
[ProdCode] [varchar] (10),
[ProdName] [varchar] (35),

[UniqueProdCode] [varchar] (10),

... many other product fields e.g. unit price, category etc...
...
)

First a small Request:
Please note that I have NOT defined links between my tables (in the diagram editor) nor have I defined Primary keys (or any constraint) for any of the tables. When you reply, please suggest I should define primary keys for the tables and also link them in the diagram editor.

THE PROBLEM:
When I do a count(*) query on the table 'SalesFACT', I get the correct number of records.

If I create a view, add table 'SalesFACT' and table ProdMaster, link the UniqueProdCode field of table 'SalesFACT' with the UniqueProdCode field of ProdMaster (so that I can also get the name, category, etc. for the products in the SalesFACT), and run a count(*) query I get a much higher and incorrect number of rows. The SQL for the view is:

SELECT dbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.UniqueProdCode

Kindly note that I have checked and the contents of the table SalesFACT' UniqueProdCode field DOES contain the correct data i.e. it contains the UniqueProdCode and NOT the ProdCode.

But if i link the "wrong fields", I get the correct count count :confused: i.e. I create a very similar view (as mentioned above) but instead link the UniqueProdCode of table SalesFACT with the ProdCode field (not the UniqueProdCode field) of ProdMaster table I get the correct count. This is really driving me nuts and I just can't understand the reverse logic. What's going on. For your convenience here is the SQL for the 2nd view:

SELECT dbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.ProdCode

Please guide... I have run out of all the things that I could check and thus this SOS and F1

Billions of thanks in advance.

Thanks & Regards.

-J

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-21 : 05:27:54

If you're getting a higher count, then UniqueProdCode in one of the tables is not unique so some records are joining more than once I suspect the prodMaster table. You need to join the prodMaster table on itself to find them something like:

select p1.UniqueProdCode
from prodMaster p1 join prodMaster p2 on p1.UniqueProdCode=p2.UniqueProdCode
group by p1.UniqueProdCode
having count(p1.UniqueProdCode)>1

;-]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-21 : 06:59:46

quote:

First a small Request:
Please note that I have NOT defined links between my tables (in the diagram editor) nor have I defined Primary keys (or any constraint) for any of the tables. When you reply, please suggest I should define primary keys for the tables and also link them in the diagram editor.

????

- Jeff

Joozh
Posting Yak Master

145 Posts

Posted - 2004-04-21 : 07:15:34

cas_0 thanks for the reply... I'll try it

Question (rather request) to Dr. Cross Join: Can you please elaborate on those '????'s. I am sorry if my quoted portion sound simple stupid but I am new to SQL and especially OLAP side of things and want to know if I should as a good data warehouse design practice do these things. I'll appreciate your suggestions/guidance/reply. Thanks.

Thanks & Regards.

-J

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-04-21 : 10:37:43

quote:
Originally posted by cas_o
select p1.UniqueProdCode
from prodMaster p1 join prodMaster p2 on p1.UniqueProdCode=p2.UniqueProdCode
group by p1.UniqueProdCode
having count(p1.UniqueProdCode)>1

For Finding duplicates no need to join itself >>
Select UniqueProdCode, count(*) NoOfDuplicateRecords
From ProdMaster group by UniqueProdCode
having count(*) > 1

- Sekar

Joozh
Posting Yak Master

145 Posts

Posted - 2004-04-22 : 04:07:27

Thanks all for your replies and help.... I turned out that the problem was with the field that I was joining with... :( Well, the problem however is solved now :)

Thanks & Regards.

-J

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources