Is there a SQL query, that list all files, which belong to selected EPDM categorise?
i would like to list all documents that belong to 3 different categories. Also with The documents, to list workflow state, and some variables such description, doc number, project no, Etc.
i don't have any experience with SQL.
Cheers.
Hey Sebastian,
Took what I posted earlier and added to it, you'll have to copy/paste lines 7-9 for each variable you want to add, and add the variable to the select list in line 5
with maxvars as (select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid from VariableValue group by DocumentID, ConfigurationID, VariableID) select d.filename, dt.TypeName, vv.ValueText as [Description] from Documents d inner join VariableValue vv on vv.DocumentID = d.DocumentID inner join maxvars mv on mv.DocumentID = vv.DocumentID and mv.ConfigurationID = vv.ConfigurationID and mv.RevisionNo = vv.RevisionNo and mv.VariableID = vv.VariableID inner join variable v on v.variableid = vv.variableid and v.VariableName = 'Description' inner join DocType dt on d.DocTypeID = dt.DocTypeID inner join status s on s.statusid = d.currentstatusid where d.deleted = 0
Hey Charley,
I thought I've got it but my code don't work. Tried different variations and have an error. below is my latest query.
with maxvars as
(select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid
from VariableValue group by DocumentID, ConfigurationID, VariableID)
select va.ValueText as [Project No.], vb.ValueText as [Customer], d.filename, dt.TypeName from Documents d
inner join VariableValue va on va.DocumentID = d.DocumentID
inner join maxvars mv on mv.DocumentID = va.DocumentID and mv.ConfigurationID = va.ConfigurationID
and mv.RevisionNo = va.RevisionNo and mv.VariableID = va.VariableID
inner join variable v on v.variableid = va.variableid and v.VariableName = 'Sales Order No'
inner join maxvars mv on mv.DocumentID = vb.DocumentID and mv.ConfigurationID = vb.ConfigurationID
and mv.RevisionNo = vb.RevisionNo and mv.VariableID = vb.VariableID
inner join variable v on v.variableid = vb.variableid and v.VariableName = 'Customer'
inner join DocType dt on d.DocTypeID = dt.DocTypeID
inner join status s on s.statusid = d.currentstatusid
where d.deleted = 0
Hey,
Close, but you have to give each copy a unique name, it's failing because there are 2 mv's, etc. I also should have said line 6-9 instead of 7-9, my mistake:
with maxvars as (select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid from VariableValue group by DocumentID, ConfigurationID, VariableID) select va.ValueText as [Project No.], vb.ValueText as [Customer], d.filename, dt.TypeName as [Category], s.Name as [State] from Documents d inner join VariableValue va on va.DocumentID = d.DocumentID inner join maxvars mv on mv.DocumentID = va.DocumentID and mv.ConfigurationID = va.ConfigurationID and mv.RevisionNo = va.RevisionNo and mv.VariableID = va.VariableID inner join variable v on v.variableid = va.variableid and v.VariableName = 'Sales Order No' 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 = 'Customer' inner join DocType dt on d.DocTypeID = dt.DocTypeID inner join status s on s.statusid = d.currentstatusid where d.deleted = 0
Thank you Charley. This is very useful.
Hey Charley,
I noticed that when I add more than 5 columns the server is unresponsive. Is there a way around it? Because I would like the table to have around 13 columns. Also is there a way to restrict the search results to only show documents with dt.TypeName as [Category] called "Procedure" or "Certificate". At the moment I don't know how to restrict the search, so all categories of docs are listed.
with maxvars as
(select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid
from VariableValue group by DocumentID, ConfigurationID, VariableID)
select va.ValueText as [Project No.], vb.ValueText as [Customer], vc.ValueText as [VDRL Document No.], vd.ValueText as [Document Title],
d.filename, dt.TypeName as [Category], s.Name as [State]
from Documents d
inner join VariableValue va on va.DocumentID = d.DocumentID
inner join maxvars mv on mv.DocumentID = va.DocumentID and mv.ConfigurationID = va.ConfigurationID
and mv.RevisionNo = va.RevisionNo and mv.VariableID = va.VariableID
inner join variable v on v.variableid = va.variableid and v.VariableName = 'Sales Order No'
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 = 'Customer'
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 = 'Doc Number'
inner join VariableValue vd on vd.DocumentID = d.DocumentID
inner join maxvars mv3 on mv3.DocumentID = vd.DocumentID and mv3.ConfigurationID = vd.ConfigurationID
and mv3.RevisionNo = vd.RevisionNo and mv3.VariableID = vd.VariableID
inner join variable v3 on v3.variableid = vd.variableid and v3.VariableName = 'Document Description'
inner join DocType dt on d.DocTypeID = dt.DocTypeID
inner join status s on s.statusid = d.currentstatusid
where d.deleted = 0
Hey Sebastian,
You'll have to break into a bit more complicated SQL to make it run with that many columns, namely pivot tables.
with maxvars as (select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid from VariableValue group by DocumentID, ConfigurationID, VariableID) select d.filename, dt.TypeName as [Category], s.Name as [State], [Document Description], [Doc Number], [Sales Order No], [Customer] from Documents d inner join DocType dt on d.DocTypeID = dt.DocTypeID inner join status s on s.statusid = d.currentstatusid inner join (SELECT vv.DocumentID, vv.ConfigurationID, VariableName, ValueText FROM VariableValue vv inner join Variable v on vv.VariableID = v.VariableID inner join maxvars mv on vv.DocumentID = mv.DocumentID and mv.ConfigurationID = vv.ConfigurationID and mv.VariableID = vv.VariableID and mv.RevisionNo = vv.RevisionNo ) AS SourceTable PIVOT ( MAX(ValueText) FOR VariableName IN ([Document Description], [Doc Number], [Sales Order No], [Customer]) ) AS PivotTable on PivotTable.DocumentID = d.DocumentID where d.deleted = 0 and (dt.TypeName = 'Procedure' or dt.TypeName = 'Certificate')
Thanks for this Charley. Tested it last night, and have 14 columns now. It works perfect.
Also the code is shorter. Really good advice.
Thanks Joy
Dear All,
2 years ago Charley Saint, did build the Query for me. I have been using is since for many different variables and documents in the EPDM vault. However, I would like to add a line to look only for files in specified locations.
I did insert the line 25, but it does not work.
Hey Sebastian,
So close, just move it above the pivot:
--edit, had to move a few other things around
with maxvars as (select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid from VariableValue group by DocumentID, ConfigurationID, VariableID) select * from (select d.filename, p.path, dt.TypeName as [Category], s.Name as [State], [Document Description], [Doc Number], [Sales Order No], [Customer] from Documents d inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID inner join projects p on p.ProjectID = dp.ProjectID inner join DocType dt on d.DocTypeID = dt.DocTypeID inner join status s on s.statusid = d.currentstatusid inner join (SELECT vv.DocumentID, vv.ConfigurationID, VariableName, ValueText FROM VariableValue vv inner join Variable v on vv.VariableID = v.VariableID inner join maxvars mv on vv.DocumentID = mv.DocumentID and mv.ConfigurationID = vv.ConfigurationID and mv.VariableID = vv.VariableID and mv.RevisionNo = vv.RevisionNo ) AS SourceTable PIVOT ( MAX(ValueText) FOR VariableName IN ([Document Description], [Doc Number], [Sales Order No], [Customer]) ) AS PivotTable on PivotTable.DocumentID = d.DocumentID where d.Deleted = 0 ) a where Path = '\EPDMVault\Projects\Current\' and (Category = 'Procedure' or Category = 'Certificate')
Thank you, Charley Saint . I tried my best to sort the code but unfortunately, as I am not a programmer I have to learn the hard way and to troubleshoot and ask for help .
For some reason, the code is still not right. The syntax is ok, I assume as I have no errors, but I don't have any results. I tried many different variations, for example, I started with the "Path", and now I just left it as "p.path".
Would the issue be related to the formatting of the file path? Would I need a wildcard or something like that?
with maxvars as
(select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid
from VariableValue group by DocumentID, ConfigurationID, VariableID)
select [Sales Order No], [Customer], [Doc Number], [Document Description],
[Priority], [Document Surce], [Revision] as [Rev.], s.Name as [State], [Submittal Date],
[Returned Date], [Approval Dedline Date] as [Dedline for approval], [Shopfloor Document Pack] as [Doc.pack], [Lead Engineer], [Sales Person], [Document Type VDRL] as [Doc. Type],
dt.TypeName as [Document Category], [Comment] as [Comments], d.filename, [Submittal Due Date], [Return Due Date], p.path
from Documents d
inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID
inner join projects p on p.ProjectID = dp.ProjectID
inner join DocType dt on d.DocTypeID = dt.DocTypeID
inner join status s on s.statusid = d.currentstatusid
inner join
(SELECT vv.DocumentID, vv.ConfigurationID, VariableName, ValueText
FROM VariableValue vv
inner join Variable v on vv.VariableID = v.VariableID
inner join maxvars mv on vv.DocumentID = mv.DocumentID and mv.ConfigurationID = vv.ConfigurationID
and mv.VariableID = vv.VariableID and mv.RevisionNo = vv.RevisionNo
) AS SourceTable
PIVOT
(
MAX(ValueText)
FOR VariableName IN ([Document Description], [Doc Number], [Sales Order No], [Customer], [Priority], [Document Surce], [Revision], [Submittal Due Date],
[Submittal Date], [Return Due Date], [Returned Date], [Lead Engineer], [Sales Person], [Approval Dedline Date],
[Document Type VDRL],[Shopfloor Document Pack], [Comment] )
) AS PivotTable on PivotTable.DocumentID = d.DocumentID
where d.deleted = 0
and p.Path = '\Projects\Current'
and (dt.TypeName = 'VDRL Document' or dt.TypeName = 'Project Procedure' or dt.TypeName = 'Project Certificate')
Ah, you're pretty close, but paths always end in a trailing slash, so you can do:
p.path = '\Projects\Current\'
or if you want everything under that folder and subfolders use:
p.path like '\Projects\Current\%'
thank you Charley Saint .
I just wish there was an easy resource for SQL in EPDM. for a beginner like me it's very difficult to implement all the ideas.
Hey Sebastian,
You'll have to break into a bit more complicated SQL to make it run with that many columns, namely pivot tables.