AnsweredAssumed Answered

Modifying SQL Query for specific PDM Variables

Question asked by John Power on Feb 7, 2019
Latest reply on Feb 11, 2019 by John Power

Like • Show 0 Likes0
Comment • 9

Hi all,

I have this query that returns file names, file path and workflow state of files in the Vault that Joy Garon posted in this thread: Queying SQL Database Basics

SELECT P.Path As 'Project Path', D.Filename AS 'File Name', S.Name AS 'Status Name' 
FROM Projects AS P INNER JOIN 
DocumentsInProjects AS DP ON P.ProjectID = DP.ProjectID INNER JOIN 
Documents AS D ON DP.DocumentID = D.DocumentID INNER JOIN 
Status AS S ON D.CurrentStatusID = S.StatusID 
Where (S.Name LIKE '%') AND D.ObjectTypeID <> 0 
Order By S.Name ASC

I was wondering if anyone could point me in the right direction to show specific variables like Revision, Description, etc..

I tried this:

select documents.Filename, status.Name, LatestRevisionNo

from documents

join status

on status.StatusId = documents.currentstatusid

But it doesn't return the Revision I expected

Any advice would be much appreciated!

Eero Luotio Feb 8, 2019 12:49 AM

Correct Answer

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

See the reply in context

No one else had this question

Outcomes

Eero Luotio Feb 8, 2019 12:49 AM

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

Actions

John Power @ Eero Luotio on Feb 10, 2019 5:56 PM

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.

====

Actions

Eero Luotio @ John Power on Feb 10, 2019 11:19 PM

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.

Actions

Sam Sam @ John Power on Feb 11, 2019 4:23 AM

Hi,

example of SQL Or + And:

https://forum.solidworks.com/thread/225499

Actions

Martin Solem @ John Power on Feb 11, 2019 5:29 AM

That's pretty cool, i didn't know that!

Actions

Martin Solem Feb 8, 2019 2:35 AM

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.

Actions

Ulf Stockburger Feb 8, 2019 8:29 AM

Hi John

Maybe this will help you. ShowAllAssignedVariables.7z

BiI SAP-PDM

Actions

Charley Saint Feb 11, 2019 4:29 PM

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)

Actions

John Power @ Charley Saint on Feb 11, 2019 5:26 PM

Thank you Charley! This works amazing!

Actions

9 Replies

Eero Luotio Feb 8, 2019 12:49 AM
Unmark Correct
Correct Answer
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
Like • Show 3 Likes3
Actions
- John Power @ Eero Luotio on Feb 10, 2019 5:56 PM
  Mark Correct
  Correct Answer
  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.
  ====
  Like • Show 1 Like1
  Actions
  - Eero Luotio @ John Power on Feb 10, 2019 11:19 PM
    Mark Correct
    Correct Answer
    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.
    Like • Show 1 Like1
    Actions
  - Sam Sam @ John Power on Feb 11, 2019 4:23 AM
    Mark Correct
    Correct Answer
    Hi,
    example of SQL Or + And:
    https://forum.solidworks.com/thread/225499
    Like • Show 0 Likes0
    Actions
  - Martin Solem @ John Power on Feb 11, 2019 5:29 AM
    Mark Correct
    Correct Answer
    That's pretty cool, i didn't know that!
    Like • Show 0 Likes0
    Actions
Martin Solem Feb 8, 2019 2:35 AM
Mark Correct
Correct Answer
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.
Like • Show 2 Likes2
Actions
Ulf Stockburger Feb 8, 2019 8:29 AM
Mark Correct
Correct Answer
Hi John

Maybe this will help you. ShowAllAssignedVariables.7z

BiI SAP-PDM
Like • Show 2 Likes2
Actions

John Power @ Charley Saint on Feb 11, 2019 5:26 PM
Mark Correct
Correct Answer
Thank you Charley! This works amazing!
Like • Show 0 Likes0
Actions

Modifying SQL Query for specific PDM Variables

Outcomes

Related Content

Recommended Content