cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

We are happy to announce the new Windchill Customization board! Learn more.

SQL script to return filevault filename for the latest version of a cad file.

colmacpro
1-Newbie

SQL script to return filevault filename for the latest version of a cad file.

Is there any way to find the filevault location and filename for the latest version of a cad filename?



Joe

10 REPLIES 10

See attached query builder report for a WTDoc's.
Can build similar for EPM Doc's.

Is there any way to do it from SQLPlus.

Designation: Non-Finmeccanica

Joe,

It sounds like you might have Intralink 9.x and don't have easy access
to the query builder functionality. I have 2 SQLPlus scripts that PTC
tech support helped me with and they might be helpful. One just reports
EPMDoc info and the other lists vault information. I've attached the
scripts.



Bob Mills

Engineering Software Administrator

DRS Integrated Defense Systems and Services

DRS Technologies, Inc

645 Anchors Street

Fort Walton Beach, Florida 32548

850-302-3241

- www.drs.com

Thank you everybody for the responses. You guys have given me plenty to work with.



joe

Has anyone out there used a sqlplus query to locate either of the following ?


1) The latest iteration of the last revision.


2) All iterations except for the last iteration of the last revision.

Ed,

You should be able to grab latest version based off the VERSIONSORTIDA2VERSIONINFO column. That's a varchar2, but you can grab those that are equal to '000001' (at least that's the pattern here, I'm assuming it's OOTB) - and it should return the latest rev

Then, there's a column called LATESTITERATIONINFO, that'll get you the latest/not latest iteration. It's a 0/1 number field.

Chris
RandyJones
19-Tanzanite
(To:colmacpro)

On 11/21/12 13:02, Ed Howanice wrote:
>
> Has anyone out there used a sqlplus query to locate either of the following ?
>
> 1) The latest iteration of the last revision.
>

For latest iteration of the latest revision I use this:

select * from
(
select ed.ida2a2, edm.cadname NAME, ed.statestate RELEASE,
ed.versionida2versioninfo REVISION,
ed.iterationida2iterationinfo ITERATION,
cast(ed.createstampa2 as timestamp) \"CREATED\",
cast(ed.modifystampa2 as timestamp) \"MODIFIED\",
cast(ed.updatestampa2 as timestamp) \"UPDATED\",
ed.ida3b10
from
epmdocumentmaster edm, epmdocument ed
where
edm.cadname = '<yourepmdocumentnamegoeshere>'
and ed.ida3masterreference = edm.ida2a2
and ed.latestiterationinfo = 1
and ed.statecheckoutinfo != 'wrk'
order by \"MODIFIED\" desc
)
where rownum=1;

> 2) All iterations except for the last iteration of the last revision.
>
>
> -----End Original Message-----


--
------------------------------------------------------------------------
Randy Jones
Systems Administrator
Great Plains Mfg., Inc.
1525 E North St
PO Box 5060
Salina, KS USA 67401
email: -
Phone: 785-823-3276
Fax: 785-667-2695
------------------------------------------------------------------------

Randy and Chris - thanks for the tips, Im getting closer but still not there yet


I have a sqlplus query that I need to locate all iterations except for the lastiteration of the last rev.


Thinking of using a MAX function for the versionsortida2versioninfo but can quite get the syntax correct.


Any ideas of how to modify the code below to find all but the last iteration of the last rev ?


select
epmdocumentmaster.name,
epmdocument.statestate,
epmdocument.latestiterationinfo,
epmdocument.versionida2versioninfo,
epmdocument.iterationida2iterationinfo,
epmdocument.IDA2A2,
epmdocumentmaster.IDA2A2,
epmdocument.IDA3MASTERREFERENCE,
epmdocument.VERSIONSORTIDA2VERSIONINFO "VSORT"
from
epmdocument,epmdocumentmaster
where
epmdocumentmaster.ida2a2=epmdocument.ida3masterreference
and
epmdocumentmaster.name like '999-%.drw'
/* epmdocument.latestiterationinfo=0 */
order by epmdocument.ida2a2
;
exit


Yes, MAX is useful.
Here is how I do it:

select * from (
select epm.ida2a2
,epm.documentnumber
,epm.name
,epm.version
,decode(decode(epm.revisionsort, epm.max_revisionsort, 1, 0)
,1, decode(epm.latestiterationinfo, 1, 'Yes', 'No')
,'No')
latest_version
,to_number(epm.revisionsort || regexp_substr(epm.version, '[[:digit:]]+'))
version_sort
from (select e.ida3d2iterationinfo
,e.ida2a2
,em.documentnumber
,em.name
,e.versionsortida2versioninfo revisionsort
,(select max(versionsortida2versioninfo)
from epmdocument mr
where mr.ida3masterreference = e.ida3masterreference)
max_revisionsort
, e.versionida2versioninfo
|| '.'
|| e.iterationida2iterationinfo
version
,e.latestiterationinfo
,e.statestate
from epmdocument e
inner join epmdocumentmaster em
on e.ida3masterreference = em.ida2a2) epm)
where latest_version = 'Yes'
and name like '999-%.drw'
order by ida2a2

joe bell
CM Application Administrator

From the responses that I recieved I bulit the SQL script below to create a text file to include with my backups.


The problem is that there are no family table generics included in the query results. What seems to be weird is that the instances of the family table models have file associated with them.



Does anybody know what is wrong with the query?



Joe



The following is the output from the custom SQL


MyAssembly_1.asm|1185522|A|2|d:\ptc\FILEVAULT\filevault_Folder_24\000000001216f2


MyPart_1.prt|1185396|A|2|d:\ptc\FILEVAULT\filevault_Folder_24\00000000121674


MyPart_1.prt|947365|A|1|d:\ptc\FILEVAULT\filevault_Folder_19\000000000e74a5


MyPart_2.prt|947364|A|1|d:\ptc\FILEVAULT\filevault_Folder_19\000000000e74a4


MyPart_3.prt|947363|A|1|d:\ptc\FILEVAULT\filevault_Folder_19\000000000e74a3

Top Tags