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
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)