Any Oracle Gurus on max function for alpha numeric (A-99) Revision

[VenkataKotra]

Any Oracle Experts ?

I'm struggling with a database extract of Windchill CAD Data with latest revision and iteration.
I've PL/SQL Procedure with max(version) which give wrong latest revisions due to revisions from A-99
Max(A-99) gives Z and Max(1-10) gives 9 as latest.

How to use to_number and to_char conditionally and use max function called in select statement of procedure.

regards
Venkata Kotra║Sr.Analyst, Engg Systems ║IS, HHI
[cid:image001.gif@01CE307A.FE4681A0]

[RajuPulavarthi]

Venkata - A query like listed below would give you the latest version and iteration for Parts. It would be similar for CAD Documents as well. The key using the 'MAX(VERSIONSORTIDA2VERSIONINFO)'

SELECT m.wtpartnumber||'|'||m.name||'|'||p.statestate||'|'||p.VERSIONIDA2VERSIONINFO||'|'
FROM wtpart p, wtpartmaster m
WHERE p.ida3masterreference=m.ida2a2
AND p.VERSIONSORTIDA2VERSIONINFO =(SELECT MAX(VERSIONSORTIDA2VERSIONINFO) from wtpart WHERE ida3masterreference=m.ida2a2)
AND p.LATESTITERATIONINFO=1
AND STATECHECKOUTINFO <>'wrk';

Best Regards,
Raju

Raju Pulavarthi
Sr. Systems Analyst - PLM Applications
Global Information Technology

[RandyJones]

On 04/04/13 11:32, Kotra, Venkata 4/16/2013 wrote:
>
> Any Oracle Experts ?
>
> I'm struggling with a database extract of Windchill CAD Data with latest revision and iteration.
>
> I've PL/SQL Procedure with max(version) which give wrong latest revisions due to revisions from A-99
>
> Max(A-99) gives Z and Max(1-10) gives 9 as latest.
>
> How to use to_number and to_char conditionally and use max function called in select statement of procedure.
>

Windchill 9.1 M070
What I use to select the latest revision and iteration is 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 = 'CadNameOfEPMObjectGoesHere'
             and ed.ida3masterreference = edm.ida2a2
             and ed.latestiterationinfo = 1
             and ed.statecheckoutinfo != 'wrk'
         order by "MODIFIED" desc
     )
where rownum=1;

Notice it is sorting by MODIFIED (epmdocument.modifystampa2) and then returning the 1st row of that selection. If your Windchill system is set up to allow checkin of earlier revisions then the above will not return the latest revision.iteration.

> regards
>
> Venkata Kotra*║*Sr.Analyst, Engg Systems *║*IS, HHI
>
> *cid:image003.gif@01CDDC3A.2CA9ECC0*
>
>
> ----------


--
------------------------------------------------------------------------
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
------------------------------------------------------------------------

[RandyJones]

On 04/04/13 13:13, Pulavarthi, Raju wrote:
>
> Venkata -- A query like listed below would give you the latest version and iteration for Parts. It would be similar for CAD Documents as well. The key using the 'MAX(VERSIONSORTIDA2VERSIONINFO)'
>
> SELECT m.wtpartnumber||'|'||m.name||'|'||p.statestate||'|'||p.VERSIONIDA2VERSIONINFO||'|'
>
> FROM wtpart p, wtpartmaster m
>
> WHERE p.ida3masterreference=m.ida2a2
>
> AND p.VERSIONSORTIDA2VERSIONINFO =(SELECT MAX(VERSIONSORTIDA2VERSIONINFO) from wtpart WHERE ida3masterreference=m.ida2a2)
>
> AND p.LATESTITERATIONINFO=1
>
> AND STATECHECKOUTINFO <>'wrk';
>

This is much simpler than the query I posted and also does not break if the Windchill system allows checkin of previous revisions.

> Best Regards,
>
> Raju
>
> *Raju Pulavarthi*
>
> Sr. Systems Analyst - PLM Applications
>
> Global Information Technology
>
> *From:*Kotra, Venkata 4/16/2013 [

[jkent]

We use this. Our revision starts at "-" then go to "A", "B", etc.. I assign an integer value to the revision and sort based on that.

SELECT
EPMDocumentMaster.CADName "CADName"
,EPMDocument.versionIdA2versionInfo "Revision"
,EPMDocument.iterationIdA2iterationInfo "Iteration"
,EPMDocument.statestate "State"
,PDMLinkProduct.namecontainerInfo "Product"
,SubFolder.name "Folder"
,CASE LEN(EPMDocument.versionIdA2versionInfo)
WHEN 1 THEN ASCII(EPMDocument.versionIdA2versionInfo) -64
WHEN 2 THEN ((ASCII(SUBSTRING(EPMDocument.versionIdA2versionInfo, 1, 1))-64)*26) + (ASCII(SUBSTRING(EPMDocument.versionIdA2versionInfo,2,1))-64)
END VersionLetterInt
FROM
EPMDocumentMaster
,EPMDocument
,PDMLinkProduct
,SubFolder
WHERE
EPMDocumentMaster.CADName = 'model_program_new.prt'
AND EPMDocument.idA3masterReference = EPMDocumentMaster.idA2A2
AND EPMDocument.idA3containerReference = PDMLinkProduct.idA2A2
AND EPMDocument.idA3B2folderingInfo = SubFolder.idA2A2
ORDER BY
VersionLetterInt ASC, Cast(EPMDocument.iterationIdA2iterationInfo AS INTEGER) ASC

Joe Kent
Engineering Systems Administrator
R&D/Leverage
Structural Brand Development / Mold Manufacturing
"With Us, Ideas Take Shape"