Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    163

    Unhappy Unanswered: Re-phrased w more details (SQL is giving different row counts)

    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 kindly 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 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 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 thansk in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    in prodMaster you heve not unique UniqueProdCode


    try this

    select UniqueProdCode from prodMaster
    group by UniqueProdCode
    having count(*)>1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •