Hello,

i can get variables with the api ("IEdmEnumeratorVariable10"). But this is very slow. I need about 500ms to get a variable value.

So I want to use an sql query. I have found an Example here:

I have changed the Query a little. This is working fine and fast for the latest Version of the File:

with maxvar as (select documentid, variableid, configurationid, max(revisionno) revisionno from variablevalue group by documentid, variableid, configurationid)  
select *
from  
(select filename, v.variablename, vv.valuetext 
from  
documents d  
inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID  
inner join projects p on p.ProjectID = dp.ProjectID  
inner join variablevalue vv on vv.DocumentID = d.DocumentID  
inner join maxvar mv on mv.DocumentID = vv.DocumentID and mv.ConfigurationID = vv.ConfigurationID  
and mv.VariableID = vv.VariableID and mv.revisionno = vv.RevisionNo 
inner join Variable v on v.VariableID = vv.VariableID  
inner join DocumentConfiguration dc on dc.ConfigurationID = vv.ConfigurationID  
where filename LIKE 'TEST.SLDASM' and d.Deleted = 0 and dc.ConfigurationName = '@'
) a  
PIVOT  
(MAX(ValueText)  
for variablename in ([Zeichnungsnummer], [Artikelnummer], [Bezeichnung1], [Bezeichnung2])  
)pivot_table  ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Now I want to read the variables for an earlier version of the File. But how do I have to change the SQL query?

I can get all Versions with:

with maxvar as (select documentid, variableid, configurationid, max(revisionno) revisionno from variablevalue group by documentid, variableid, configurationid)  
select *
from  
(select filename, v.variablename, vv.valuetext, vv.RevisionNo  --add vv.RevisionNo
from  
documents d  
inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID  
inner join projects p on p.ProjectID = dp.ProjectID  
inner join variablevalue vv on vv.DocumentID = d.DocumentID  
inner join maxvar mv on mv.DocumentID = vv.DocumentID and mv.ConfigurationID = vv.ConfigurationID  
and mv.VariableID = vv.VariableID -- and mv.revisionno = vv.RevisionNo 
inner join Variable v on v.VariableID = vv.VariableID  
inner join DocumentConfiguration dc on dc.ConfigurationID = vv.ConfigurationID  
where filename LIKE 'TEST.SLDASM' and d.Deleted = 0 and dc.ConfigurationName = '@'
) a  
PIVOT  
(MAX(ValueText)  
for variablename in ([Zeichnungsnummer], [Artikelnummer], [Bezeichnung1], [Bezeichnung2])  
)pivot_table  ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I get a Table with (Example):

If i only filter by RevisionNo i don´t get the right values.

See S-014197:

Be aware that only "unique" values are stored in the  ‘VariableValue’ table . In other words, if there are no changes to a variable value over several file versions, only the most recent value is stored in the table. 

But how can i get the right values for a specific Version?

Regards

Sascha