AnsweredAssumed Answered

SQL Query Duplicate Values Help

Question asked by Sergejs Sinicins on Jun 7, 2019
Latest reply on Jun 12, 2019 by Sergejs Sinicins

Like • Show 0 Likes0
Comment • 7

Hello everybody!

I'm trying to get the data from EPDM into Excel via Query 1 (see below). It works (in a way) but for some reason it extracts duplicates values despite I'm using MAXVARS.

Query 1

with maxvars as
(select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid
from VariableValue group by DocumentID, ConfigurationID, VariableID)

select DISTINCT d.filename, va.ValueText as [M-Number], va.ValueText as [Description], va.ValueText as [Customer SN], w.name as [Workflow] -- the names in [] are the column names returned below
from Documents d

inner join status s on s.StatusID = d.CurrentStatusID
inner join workflows w on w.WorkflowID = s.WorkflowID

inner join VariableValue va on va.DocumentID = d.DocumentID
inner join maxvars mv on mv.DocumentID = va.DocumentID and mv.RevisionNo = va.RevisionNo and mv.ConfigurationID = va.ConfigurationID --and mv.VariableID = va.VariableID
inner join variable v on v.variableid = va.variableid and v.VariableName = 'Description' --edit this Variable name to search for

inner join VariableValue vb on vb.DocumentID = d.DocumentID
inner join maxvars mv1 on mv1.DocumentID = vb.DocumentID and mv1.ConfigurationID = vb.ConfigurationID and mv1.RevisionNo = vb.RevisionNo and mv1.VariableID = vb.VariableID
inner join variable v1 on v1.variableid = vb.variableid and v1.VariableName = 'Number O-Code' --edit this Variable name to search for

inner join VariableValue vc on vc.DocumentID = d.DocumentID
inner join maxvars mv2 on mv2.DocumentID = vc.DocumentID and mv2.ConfigurationID = vc.ConfigurationID and mv2.RevisionNo = vc.RevisionNo and mv2.VariableID = vc.VariableID
inner join variable v2 on v2.variableid = vc.variableid and v2.VariableName = 'Number Customer' --edit this Variable name to search for

where d.deleted = 0
and w.Name = 'CAD Data' --Workflow name
and mv.configurationID = 2
and mv1.ConfigurationID = 2
and mv2.ConfigurationID = 2
and d.filename LIKE 'G-___________' --filename WITH extension
or d.filename LIKE 'M-___________' --filename WITH extension

ORDER BY D.Filename

There are no duplicate file names in the system.

Query result:

When I've tried to add the LATEST version but it went from bad to worse:

I just can't understand why is it doing it, and more important how to fix it.

The end result I want to see is just ONE line with data extracted from "@" configuration.

Thanks

Sergejs

Charley Saint Jun 11, 2019 11:27 AM

Correct Answer

You're returning mv.revisionno as version, but mv is joined to VA which is where you are getting description from so it's just telling you that description was last set in version 5 (because variablevalue only stores changes to variables)

See the reply in context

No one else had this question

Outcomes

Michael Dekoning Jun 7, 2019 6:19 AM

I'm sorry I do not have the time currently to troubleshoot your entire query. However I will tell you that the DISTINCT function requires that every value in the columns must be unique in all rows. Therefore the fact that the Description and Customer SN values vary is why your query returns 4 rows.

Also you say you only want the values from the @ configuration but I don't see a where clause with the configuration name equal to '@'.

Hope this helps.

Actions

Charley Saint Jun 7, 2019 1:07 PM

Couple of questions, why is your variableid comparison commented out here:

inner join maxvars mv on mv.DocumentID = va.DocumentID and mv.RevisionNo = va.RevisionNo and mv.ConfigurationID = va.ConfigurationID --and mv.VariableID = va.VariableID

and why is everything in the select line va instead of va, vb, vc:

select DISTINCT d.filename, va.ValueText as [M-Number], va.ValueText as [Description], va.ValueText as [Customer SN]

Actions

Sergejs Sinicins @ Charley Saint on Jun 10, 2019 3:53 AM

Good spot. Copy/paste error.

this is what I'm using now.

with maxvars as
(select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid
from VariableValue group by DocumentID, ConfigurationID, VariableID)

select distinct d.filename, vb.ValueText as [M-Number], va.ValueText as [Description], vc.ValueText as [Customer SN], w.name as [Workflow], mv.RevisionNo as [Version] -- the names in [] are the column names returned below
from Documents d

inner join status s on s.StatusID = d.CurrentStatusID
inner join workflows w on w.WorkflowID = s.WorkflowID

inner join VariableValue va on va.DocumentID = d.DocumentID
inner join maxvars mv on mv.DocumentID = va.DocumentID and mv.ConfigurationID = va.ConfigurationID and mv.RevisionNo = va.RevisionNo and mv.VariableID = va.VariableID
inner join variable v on v.variableid = va.variableid and v.VariableName = 'Description' --edit this Variable name to search for

ORDER BY D.Filename, mv.revisionno Desc

Actions

Charley Saint @ Sergejs Sinicins on Jun 10, 2019 1:30 PM

I think you are accidentally returning values from deleted files because of the OR in the where clause, try changing the last 2 lines in there to this:

and (d.filename LIKE 'G-___________' --filename WITH extension
or d.filename LIKE 'M-___________') --filename WITH extension

Actions

Sergejs Sinicins @ Charley Saint on Jun 11, 2019 4:19 AM

Charley,

It worked! BUT

Before it was pulling many versions of the same file like that:

After I've changed the lines as you recommended I'm getting this:

For some reason now it's pulling version 5, however latest version is 13.

I'm lost. Please suggest.

Thanks

Sergejs

Actions

Charley Saint @ Sergejs Sinicins on Jun 11, 2019 11:27 AM

Actions

Sergejs Sinicins @ Charley Saint on Jun 12, 2019 4:30 AM

Charley,

That make sense now. Thanks for your help. Case closed!

Sergejs

Actions

7 Replies

Michael Dekoning Jun 7, 2019 6:19 AM
Mark Correct
Correct Answer
I'm sorry I do not have the time currently to troubleshoot your entire query. However I will tell you that the DISTINCT function requires that every value in the columns must be unique in all rows. Therefore the fact that the Description and Customer SN values vary is why your query returns 4 rows.
Also you say you only want the values from the @ configuration but I don't see a where clause with the configuration name equal to '@'.
Hope this helps.
Like • Show 0 Likes0
Actions
Charley Saint Jun 7, 2019 1:07 PM
Mark Correct
Correct Answer
Couple of questions, why is your variableid comparison commented out here:

inner join maxvars mv on mv.DocumentID = va.DocumentID and mv.RevisionNo = va.RevisionNo and mv.ConfigurationID = va.ConfigurationID --and mv.VariableID = va.VariableID

and why is everything in the select line va instead of va, vb, vc:

select DISTINCT d.filename, va.ValueText as [M-Number], va.ValueText as [Description], va.ValueText as [Customer SN]
Like • Show 0 Likes0
Actions
- Sergejs Sinicins @ Charley Saint on Jun 10, 2019 3:53 AM
  Mark Correct
  Correct Answer
  Good spot. Copy/paste error.
  this is what I'm using now.
  
  with maxvars as
  (select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid
  from VariableValue group by DocumentID, ConfigurationID, VariableID)
  
  select distinct d.filename, vb.ValueText as [M-Number], va.ValueText as [Description], vc.ValueText as [Customer SN], w.name as [Workflow], mv.RevisionNo as [Version] -- the names in [] are the column names returned below
  from Documents d
  inner join status s on s.StatusID = d.CurrentStatusID
  inner join workflows w on w.WorkflowID = s.WorkflowID
  inner join VariableValue va on va.DocumentID = d.DocumentID
  inner join maxvars mv on mv.DocumentID = va.DocumentID and mv.ConfigurationID = va.ConfigurationID and mv.RevisionNo = va.RevisionNo and mv.VariableID = va.VariableID
  inner join variable v on v.variableid = va.variableid and v.VariableName = 'Description' --edit this Variable name to search for
  inner join VariableValue vb on vb.DocumentID = d.DocumentID
  inner join maxvars mv1 on mv1.DocumentID = vb.DocumentID and mv1.ConfigurationID = vb.ConfigurationID and mv1.RevisionNo = vb.RevisionNo and mv1.VariableID = vb.VariableID
  inner join variable v1 on v1.variableid = vb.variableid and v1.VariableName = 'Number O-Code' --edit this Variable name to search for
  
  inner join VariableValue vc on vc.DocumentID = d.DocumentID
  inner join maxvars mv2 on mv2.DocumentID = vc.DocumentID and mv2.ConfigurationID = vc.ConfigurationID and mv2.RevisionNo = vc.RevisionNo and mv2.VariableID = vc.VariableID
  inner join variable v2 on v2.variableid = vc.variableid and v2.VariableName = 'Number Customer' --edit this Variable name to search for
  where d.deleted = 0
  and w.Name = 'CAD Data' --Workflow name
  and mv.configurationID = 2
  and mv1.ConfigurationID = 2
  and mv2.ConfigurationID = 2
  and d.filename LIKE 'G-___________' --filename WITH extension
  or d.filename LIKE 'M-___________' --filename WITH extension
  ORDER BY D.Filename, mv.revisionno Desc
  Like • Show 0 Likes0
  Actions
  - Charley Saint @ Sergejs Sinicins on Jun 10, 2019 1:30 PM
    Mark Correct
    Correct Answer
    I think you are accidentally returning values from deleted files because of the OR in the where clause, try changing the last 2 lines in there to this:
    and (d.filename LIKE 'G-___________' --filename WITH extension
    or d.filename LIKE 'M-___________') --filename WITH extension
    Like • Show 0 Likes0
    Actions
    - Sergejs Sinicins @ Charley Saint on Jun 11, 2019 4:19 AM
      Mark Correct
      Correct Answer
      Charley,
      
      It worked! BUT
      
      Before it was pulling many versions of the same file like that:
      After I've changed the lines as you recommended I'm getting this:
      
      For some reason now it's pulling version 5, however latest version is 13.
      I'm lost. Please suggest.
      
      Thanks
      Sergejs
      
      Like • Show 0 Likes0
      Actions
      - Charley Saint @ Sergejs Sinicins on Jun 11, 2019 11:27 AM
        Unmark Correct
        Correct Answer
        You're returning mv.revisionno as version, but mv is joined to VA which is where you are getting description from so it's just telling you that description was last set in version 5 (because variablevalue only stores changes to variables)
        Like • Show 2 Likes2
        Actions
        Sergejs Sinicins @ Charley Saint on Jun 12, 2019 4:30 AM
        Mark Correct
        Correct Answer
        Charley,
        
        That make sense now. Thanks for your help. Case closed!
        
        Sergejs
        Like • Show 0 Likes0
        Actions

SQL Query Duplicate Values Help

Outcomes

Related Content

Recommended Content