AnsweredAssumed Answered

SQL Query for variable values in a file with version

Question asked by Sascha Smolny on Feb 17, 2020
Latest reply on Feb 26, 2020 by Sascha Smolny

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:

SQL query for multiple epdm variable values in a file 

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

filenameRevisionNoZeichnungsnummerArtikelnummerBezeichnung1Bezeichnung2
TEST.SLDASM1TESTTestTestTest
TEST.SLDASM2
TEST.SLDASM3NULLNULLNULLNULL
TEST.SLDASM4NULLNULLBez1Bez2
TEST.SLDASM5NULLNULLNULL
TEST.SLDASM6NULLNULLNULLNULL

 

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

Outcomes