SQL to Fix Filename=Name=Number?

Joe,

What's the logic to fix them. In other words, if Filename, Name and Number arenot equalwhich value will you use to populate the other two. Below I assumed CADName

I'd do it this way:

Create an Info*Engine task that contanins two webjects

1.Query-Objects webject queryfor all EPMDocumentsMasters. You need to edit this query to limit the number of masters returned

2.Query-Objects webject queryfor type = wt.epm.EPMDocumentsMaster where name!=CADName|number!=CADName

3. Change-Identity webject to do the rename/renumber

The code is below. Past it in a file named fixNames.xml

Put fixNames.xml into WT_HOME\tasks\ext\tools

Login to Windchill test system and edit the URL:

http://<
%>

WARNING: Limit this query do not use it as it as it will try to return all EPMDocumentMasters.

<ie:webject name="Query-Objects" type="OBJ">

<ie:param name="INSTANCE" data="&lt;%=instance%">"/>

<ie:param name="TYPE" data="WCTYPE|wt.epm.EPMDocumentMaster"/">

<ie:param name="WHERE" data="number=*"/">

<ie:param name="ATTRIBUTE" data="*"/">

<ie:param name="GROUP_OUT" data="output"/"></ie:webject>

Find those that do not match
<ie:webject name="Query-Objects" type="OBJ">

<ie:param name="INSTANCE" data="&lt;%=instance%">"/>

<ie:param name="TYPE" data="WCTYPE|wt.epm.EPMDocumentMaster"/">

<ie:param name="WHERE" data="number=${output[*]number[*]}"/">

<ie:param name="WHERE" data="CADName!=${output[*]number[*]}"/">

<ie:param name="ATTRIBUTE" data="*"/">

<ie:param name="WHERE_CASE_SENSITIVITY" data="FALSE"/">

<ie:param name="GROUP_OUT" data="output2"/">

</ie:webject>

Rename/Renumber those that do not match
<ie:webject name="Change-Identity" type="OBJ">

<ie:param name="INSTANCE" data="&lt;%=instance%">"/>

<ie:param name="OBJECT_REF" data="${output2[*]obid[*]}"/">

<ie:param name="FIELD" data="name=${output2[*]CADName[*]}"/">

<ie:param name="FIELD" data="number=${output2[*]CADName[*]}"/">

<ie:param name="ATTRIBUTE" data="*"/">

</ie:webject>

in SQL

Before you do this beware that SQL updates simply edit the value. There is no check to see if that number is already in the dB.

Make sure the target numbers don't exist before doing the update. Otherwise you'll have two Masters with the same number which is obviously not good.

this query will return the target numbers that already exist. You MUST exclude these numbers from the update below.

select documentnumber from EPMDocumentMaster where documentnumber in (select upper(CADName) from EPMDocumentMaster where DocumentNumber!=upper(CADName));

this update statement will do a mass update. DO NOT use this unless the query above returns nothing.

update EPMDcumentMaster set Name=CADName where upper(Name)!=upper(CADName);

update EPMDcumentMaster set DocumentNumber=upper(CADName) where DocumentNumber!=upper(CADName);

Hope this helps,

David

In Reply to Joe Barnes:

Does anybody know of a way fix any objects that may not have Filename= Name=Number?

We have some that are not correct and would like to fix them.

I was hoping it would be a simple piece of SQL.

Joe

I followed your instructions but when I connected to the URL after logging in I just got a blank page and nothing changed in the database. Your code did remind me of a SELECT statement I had for something elsething which led me to the UPDATE SQL statement below.

>sqlplus

SET NAME=upper(CADNAME), DOCUMENTNUMBER=upper(CADNAME)

upper(NAME)<>upper(CADNAME) OR upper(DOCUMENTNUMBER)<>upper(CADNAME);

The UPDATE statement seems to have worked, but right now I need to test it a little more before trying it on the production system.

Joe

Does anybody see anything wrong with doing this?

When I talked to PTC Tech support they told me they did not have or support anything. They then referred me to the PTC GS team for a possible tool or utility..

Joe

Joe,

Making updates directly in the database is generally not recommended. In this case, the EPMDocument identity information is also stored in the EPMDocumentMasterKey table and might get out of sync if the EPMDOcumentMaster table is updated through a SQL statement

Instead, the preferred approach for updating the EPMDocument name and number is by using the following API

IdentityHelper.service.changeIdentity

Ravi