Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

My view return more records that are in SALG_SALGSDATA

Hi!

Have a table called SALG_SALGSDATA

If i use this query:

select count(*) from SALG_SALGSDATA
It return :
2396677 records

But if i create this view

CREATE VIEW
    SALG_STATISTIKK7
    (
        Dimension,
        Description1,
        Dimension2,
        Description2,
        Dimension3,
        Description3,
        Itemid,
        ItemName,
        EDIEpdNo,
        ForsendelsesNr,
        GrossistGLN,
        FakturaNr,
        Fakturadato,
        GrossistKundeNr,
        KundeGLN,
        TSLopenr,
        Kundenavn,
        KundeAdresse,
        Kundepostnr,
        KundePoststed,
        FakturaDebKredKod,
        FakturalinjeNr,
        Pakningsnivaa,
        Gtin,
        GrossistVarenr,
        EPDNr,
        LeverandørVarenr,
        DebKredkodeFL,
        Engrospris,
        GrossistVaretekst,
        GrossistAntalliPakke,
        MVASats,
        FakturaEnhetsType,
        AntFaktEnheter,
        Beregnetantall,
        GLNLeverandor,
        Kjede1,
        Kjedenavn1,
        År,
        Uke,
        Måned,
        Kjede3,
        Kjedenavn3,
        Kjede4,
        Kjedenavn4,
        Fakturertbeløp
    ) AS
SELECT
    '',
     '',    
        '',
     '',
      '',
     '',
        '',
     '',
       '',
    REG.ForsendelsesNr,
    REG.GrossistGLN,
    REG.FakturaNr,
    REG.Fakturadato,
    REG.GrossistKundeNr,
    REG.KundeGLN,
    REG.TSLopenr,
    REG.Kundenavn,
    REG.KundeAdresse,
    RIGHT('0000' + CONVERT(VARCHAR,REG.Kundepostnr), 4),
    REG.KundePoststed,
    REG.FakturaDebKredKod,
    REG.FakturalinjeNr,
    REG.Pakningsnivaa,
    REG.Gtin,
    REG.GrossistVarenr,
    REG.EPDNr,
    REG.LeverandørVarenr,
    REG.DebKredkodeFL,
    REG.Engrospris,
    REG.GrossistVaretekst,
    REG.GrossistAntalliPakke,
    REG.MVASats,
    REG.FakturaEnhetsType,
    REG.AntFaktEnheter,
    REG.Beregnetantall,
    REG.GLNLeverandor,
    REG.GrossistKundeNr 'Kjede1',
    '',
    YEAR(REG.Fakturadato)          AS 'År',
    DATEPART(week,REG.Fakturadato) AS 'Uke',
    MONTH(REG.Fakturadato)         AS 'Måned',
    '',
    '' 'Kjedenavn3',
    FT.RKjede2 'Kjede4',
    '' 'Kjedenavn4',
    (REG.Engrospris * REG.AntFaktEnheter)
FROM
    dbo.KJE_FLINKTOTAL FT
RIGHT JOIN
   dbo.SALG_SALGSDATA REG
ON
   FT.LoepeNr = REG.TSLopenr

Open in new window


And i run this query:
select count(*) from SALG_STATISTIKK7'
It return over 5 millions records ???

How is this possible ?
0
team2005
Asked:
team2005
  • 8
  • 6
1 Solution
 
CluskittCommented:
That is because you have various records in dbo.KJE_FLINKTOTAL that have the same LoepeNr. That means that, for each one, there will be a new row. So, if you have, for example, 10 records with the same LoepeNr, your query will return 2396677 + 9
0
 
CluskittCommented:
To further explain:
Imagine tables TableA (IDa, col1) and TableB (IDb, FK_IDa, col2)
TableA has two rows:
IDa     col1
1         val1
2         val2

TableB has 3 rows:
IDb    FK_IDa    col2
1        1             val11
2        1             val12
3        1             val13

When you do:
SELECT * FROM TableA LEFT JOIN TableB ON FK_IDa=IDa
you will get 4 records returned:
IDa    col1    IDb    FK_IDa    col2
1        val1    1        1             val11
1        val1    2        1             val12
1        val1    3        1             val13
2        val2    NULL NULL      NULL
0
 
team2005Author Commented:
Hi!

Ok, i figer out that table dbo.KJE_FLINKTOTAL
contains of many records that have the same
FT.LoepeNr = REG.TSLopenr

One of the fields in dbo.KJE_FLINKTOTAL is a date field

Example of data from dbo.KJE_FLINKTOTAL

LoepeNr

10020        2010-12-01
10020        2012-01-02
10030        2010-02-04
10030        2010-04-05
10030        2010-11-11
10040        2008-10-10
10040        2009-11-02
10040        2010-01-04
10050        2010-04-04
10050        2011-05-06


So i must use MAX to get only the record with the newest date.

I tryed this this at the last two lines of the view:

ON
    FT.LoepeNr = REG.TSLopenr
AND FT.DatoTil=(SELECT MAX(FT.DatoTil))

Open in new window


But this dosent work ?
Gives me this error message :
 [Error Code: 1015, SQL State: S1000]  An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.

Whats wrong
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
CluskittCommented:
FROM dbo.KJE_FLINKTOTAL FT
RIGHT JOIN dbo.SALG_SALGSDATA REG
ON FT.LoepeNr = REG.TSLopenr
  AND FT.DatoTil=(SELECT MAX(FT.DatoTil) FROM KJE_FLINKTOTAL WHERE LoepeNr = REG.TSLopenr)
0
 
team2005Author Commented:
Hi!

Tryed the query, but get this error message

 [Error Code: 1015, SQL State: S1000]  An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.

?
0
 
CluskittCommented:
Ah, yes. It was a typo that I didn't notice. Just remove the FT. from the subquery:
FROM dbo.KJE_FLINKTOTAL FT
RIGHT JOIN dbo.SALG_SALGSDATA REG
ON FT.LoepeNr = REG.TSLopenr
  AND FT.DatoTil=(SELECT MAX(DatoTil) FROM KJE_FLINKTOTAL WHERE LoepeNr = REG.TSLopenr)

It was trying to do a MAX on the outer table, not the subquery one.
0
 
team2005Author Commented:
Hi!

Still got to many records ?
0
 
CluskittCommented:
Probably you also have some records that have the same DatoTil and LoepeNr. Try this:
SELECT t.*
FROM KJE_FLINKTOTAL t
INNER JOIN (SELECT DatoTil dt, LoepeNr nr FROM KJE_FLINKTOTAL GROUP BY DatoTil, LoepeNr HAVING COUNT(*)>1) ON DatoTil=dt AND LoepeNr=nr

This should return all repeated records. You can then decide if they should be eliminated or if the query should be further refined
0
 
team2005Author Commented:
Hi!

Get this error message, when tryed your query:
[Error Code: 156, SQL State: S1000]  Incorrect syntax near the keyword 'ON'.
0
 
CluskittCommented:
Sorry, you need to add an alias to the subquery:
SELECT t.*
FROM KJE_FLINKTOTAL t
INNER JOIN (SELECT DatoTil dt, LoepeNr nr FROM KJE_FLINKTOTAL GROUP BY DatoTil, LoepeNr HAVING COUNT(*)>1) s ON DatoTil=dt AND LoepeNr=nr
0
 
team2005Author Commented:
Hi!

Aha, now i get a list of Duplicates ?

So how do i change this, so i get this right (no duplicates)

FROM dbo.KJE_FLINKTOTAL FT
RIGHT JOIN dbo.SALG_SALGSDATA REG
ON FT.LoepeNr = REG.TSLopenr
  AND FT.DatoTil=(SELECT MAX(DatoTil) FROM KJE_FLINKTOTAL WHERE LoepeNr = REG.TSLopenr)

Open in new window


Or maby i need to delete the duplicates ?
0
 
CluskittCommented:
That depends. If they are simply duplicates, you can either delete them, or you can join with a subquery. If they aren't duplicates but have some other different fields, you can include them in your query. For the subquery:

FROM (SELECT DISTINCT LoepeNr, MAX(DatoTil) DatoTil
    FROM KJE_FLINKTOTAL GROUP BY LoepeNr) FT
RIGHT JOIN dbo.SALG_SALGSDATA REG
ON FT.LoepeNr = REG.TSLopenr

If you want more fields displayed, beside LoepeNr, then you need to add it to both the select and the group.
0
 
team2005Author Commented:
Hi!

Thank you for your BIG help. :)
0
 
CluskittCommented:
Glad to help. Let me just point out, though, that the DISTINCT in the above query isn't necessary and should be removed. The GROUP BY will already cause a distinct.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now