-
Re: Modifying SQL Query for specific PDM Variables
Eero Luotio Feb 8, 2019 12:49 AM (in response to John Power)First query correct variable id for variable to use in your query.
SELECT [VariableID]
,[VariableName]
,[VariableType]
,[IsDeleted]
,[FlagUnique]
,[FlagMandatory]
,[FlagFreeUpdateAllVersion]
,[FlagFreeUpdateLatestVersion]
FROM [VAULT_NAME].[dbo].[Variable]
where variablename like '%revision%'
Then innerjoin VariableValue table to your query earlier query
Then add valuetext from variablevalue table to select.
Example:
Good way to test your queries is using SELECT TOP 10 to only select top 10 rows, makes testing much faster.
Another way you can use is find some known file from PDM and use it in WHERE clause. Example:
Where DP.Deleted = 0 and VV.VariableID = 61 and D.Filename like 'filename.slpdrt'
I have added here to Distinct to skip duplicate rows.
This will query all revisions of file by that variable, so you still have probably some work to do.
SELECT DISTINCT P.Path As 'Project Path', DP.DocumentID as DocumentID, D.Filename, VV.ValueText
FROM Projects AS P INNER JOIN
DocumentsInProjects AS DP ON P.ProjectID = DP.ProjectID
INNER JOIN
Documents as D ON D.DocumentID = DP.DocumentID
INNER JOIN
VariableValue as VV ON VV.DocumentID = D.DocumentID
Where DP.Deleted = 0 and VV.VariableID = 61
-
Re: Modifying SQL Query for specific PDM Variables
John Power Feb 10, 2019 5:56 PM (in response to Eero Luotio)Thanks Eero that worked quite well, and thank you everyone for your help and suggestions I really appreciate it!
One last question... Is it possible to have multiple Table Headings to query for extra columns? eg..
VariableValue as VV ON VV.DocumentID = D.DocumentID
Where DP.Deleted = 0 and VV.VariableID = 61
VariableValue as DD ON DD.DocumentID = D.DocumentID
Where DP.Deleted = 0 and VV.VariableID = 43
=====
Also while doing some research on the forums I came across this where you can let PDM Build Queries for you:
1) Create the column set for the fields you want returned
2) Run the search (C:\Program Files\SOLIDWORKS Corp\SOLIDWORKS PDM\search.exe) as administrator
3) Change the registry setting:
Create a new DWORD value called DebugSQL under HKEY_LOCAL_MACHINE\SOFTWARE\SolidWorks\Applications\PDMWorks Enterprise Set it to 1 When running a search, the search query will be created in a file called c:\search.txt
4) Paste the contents of c:\search.txt into a new query.
Every time a search is run, c:\search.txt will be updated with the new query.
====
-
Re: Modifying SQL Query for specific PDM Variables
Eero Luotio Feb 10, 2019 11:19 PM (in response to John Power)I would say nope, but you could use #temp table or try to make where clause where you have brackets.
Dunno exactly what you are trying to achieve but something like this:
Where (DP.Deleted = 0 and VV.VariableID = 61 and D.Filename like 'OT00032681.SLDPRT') or (DP.Deleted = 0 and VV.VariableID = 43)
The infromation about that search registry key is something that I'm not familiar with and seems like I really have to test that one out.
-
Re: Modifying SQL Query for specific PDM Variables
Sam Sam Feb 11, 2019 4:23 AM (in response to John Power) -
Re: Modifying SQL Query for specific PDM Variables
Martin Solem Feb 11, 2019 5:29 AM (in response to John Power)That's pretty cool, i didn't know that!
-
-
-
Re: Modifying SQL Query for specific PDM Variables
Martin Solem Feb 8, 2019 2:35 AM (in response to John Power)Just to add to the confusion: Revisions in the database are versions, so as Eero Luotio points out, you're better off getting the variable value.
-
Re: Modifying SQL Query for specific PDM Variables
Ulf Stockburger Feb 8, 2019 8:29 AM (in response to John Power) -
Re: Modifying SQL Query for specific PDM Variables
Charley Saint Feb 11, 2019 4:29 PM (in response to John Power)Hey John,
I use this report all the time. It's pretty straightforward on how to add variable columns and filter based on path or specific configurations. It's not the fastest report in the world which could be fixed but then it'd be much harder to configure, hope it helps.
declare @report_cols as varchar(500) set @report_cols = '[Description], [Part Number]' -- maintain this format no comma after last entry declare @filter_path as varchar(250) set @filter_path = '\Projects\' -- don't forget trailing slash, for entire vault use just \, but for speed limit it to certain folders declare @filter_configs as varchar(250) set @filter_configs = '(''@'', ''Default'')' -- maintain this format, comment below to not filter configs, for just @ use '(''@'')' declare @sql as varchar(max) set @sql = 'with maxvar as (select documentid, configurationid, variableid, max(RevisionNo) revisionno from VariableValue group by DocumentID, ConfigurationID, VariableID) select path, filename, ConfigurationName, ' + @report_cols + ' -- replace , with + to not split up file and path from ( select p.path, filename, dc.ConfigurationName, variablename, vv.valuecache from documents d inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID inner join projects p on p.projectid = dp.ProjectID inner join ProjectTreeRec ptr on ptr.ChildProjectID = dp.ProjectID inner join Projects p2 on p2.ProjectID = ptr.ParentProjectID inner join VariableValue vv on vv.DocumentID = d.DocumentID inner join maxvar mv on vv.DocumentID = mv.DocumentID and vv.ConfigurationID = mv.ConfigurationID and vv.VariableID = mv.VariableID and vv.RevisionNo = mv.revisionno inner join variable v on v.VariableID = vv.VariableID inner join DocumentConfiguration dc on dc.ConfigurationID = vv.ConfigurationID where d.Deleted = 0 and dp.Deleted = 0 -- ignore deleted files and p2.path = ''' + @filter_path + ''' -- filter to only files in a specific folder and dc.configurationname in ' + @filter_configs + ' -- comment out this line to return all configs ) a pivot( max(valuecache) for variablename in (' + @report_cols + ') ) b' execute(@sql)
-
Re: Modifying SQL Query for specific PDM Variables
John Power Feb 11, 2019 5:26 PM (in response to Charley Saint)Thank you Charley! This works amazing!
-