QuerySpec sort EPMDocument iteration as an integer vs as text

[RandyJones]

Windchill 9.1 M050

I have the following QuerySpec:

String epmNo = "someEPMDocumentNumber";
QuerySpec qs = new QuerySpec(EPMDocument.class);
ClassAttribute number = new ClassAttribute(EPMDocument.class, EPMDocument.NUMBER);
ClassAttribute versionSort = new ClassAttribute(EPMDocument.class,
"versionInfo.identifier.versionSortId");
ClassAttribute iteration = new ClassAttribute(EPMDocument.class,
"iterationInfo.identifier.iterationId");
qs.appendWhere(new SearchCondition(EPMDocument.class, EPMDocument.NUMBER,
SearchCondition.LIKE, epmNo), null);
qs.appendOrderBy(new OrderBy(number, true));
qs.appendOrderBy(new OrderBy(versionSort, true));
qs.appendOrderBy(new OrderBy(iteration, true));
StatementSpec statementSpec = (StatementSpec) qs;
qr = PersistenceHelper.manager.find(statementSpec);

Where I am searching for "someEPMDocumentNumber" and then sorting by the number,
version (versionSortId), and the iteration. This is working like I want except
for the iteration part. This is getting sorted like text so for example on an
revision with 12 iterations I am getting results like this:
Rev.Iteration
A.9
A.8
A.7
A.6
A.5
A.4
A.3
A.2
A.12
A.11
A.10
A.1

When I want it to sort like this:
A.12
A.11
A.10
A.9
A.8
A.7
A.6
A.5
A.4
A.3
A.2
A.1

So how do I define the query to sort the iteration as an integer?

--

[ddemay]

Sounds like a bug with using OrderBy?

I never use it and instead build java comparator classes for lists of results or input.




Sent from my Verizon Wireless BlackBerry

[RandyJones]

On 05/08/12 16:33, David DeMay wrote:
> Sounds like a bug with using OrderBy?

No I wouldn't call it a bug with OrderBy because the iterationida2iterationinfo
column is defined as a varchar2 column in the database so technically it is
sorting correct (as text). If I was creating a query for a mysql table I would
simply do this:
...
order by cast(`iterationida2iterationinfo` as SIGNED)

So I would coerce mysql into treating the field as an integer and let the
database do all the work of sorting because:
1. the query is simple
2. databases are proficient at sorting (generally much faster than you can do it
in some program code)

So the question is how to use QuerySpec and or some undocumented OrderBy magic
to sort a particular attribute as an integer?


>
> I never use it and instead build java comparator classes for lists of results or input.
>
>
>
>
> Sent from my Verizon Wireless BlackBerry
>
> -----Original Message-----
> From: Randy Jones<->
> Date: Tue, 08 May 2012 16:03:48
> To:<->
> Reply-To: Randy Jones<->
> Subject: [solutions] - QuerySpec sort EPMDocument iteration as an integer vs
>   as text
>
> Windchill 9.1 M050
>
> I have the following QuerySpec:
>
> String epmNo = "someEPMDocumentNumber";
> QuerySpec qs = new QuerySpec(EPMDocument.class);
> ClassAttribute number = new ClassAttribute(EPMDocument.class, EPMDocument.NUMBER);
> ClassAttribute versionSort = new ClassAttribute(EPMDocument.class,
> "versionInfo.identifier.versionSortId");
> ClassAttribute iteration = new ClassAttribute(EPMDocument.class,
> "iterationInfo.identifier.iterationId");
> qs.appendWhere(new SearchCondition(EPMDocument.class, EPMDocument.NUMBER,
> SearchCondition.LIKE, epmNo), null);
> qs.appendOrderBy(new OrderBy(number, true));
> qs.appendOrderBy(new OrderBy(versionSort, true));
> qs.appendOrderBy(new OrderBy(iteration, true));
> StatementSpec statementSpec = (StatementSpec) qs;
> qr = PersistenceHelper.manager.find(statementSpec);
>
> Where I am searching for "someEPMDocumentNumber" and then sorting by the number,
> version (versionSortId), and the iteration. This is working like I want except
> for the iteration part. This is getting sorted like text so for example on an
> revision with 12 iterations I am getting results like this:
> Rev.Iteration
> A.9
> A.8
> A.7
> A.6
> A.5
> A.4
> A.3
> A.2
> A.12
> A.11
> A.10
> A.1
>
> When I want it to sort like this:
> A.12
> A.11
> A.10
> A.9
> A.8
> A.7
> A.6
> A.5
> A.4
> A.3
> A.2
> A.1
>
> So how do I define the query to sort the iteration as an integer?
>


--

[ddemay]

What does the IterationIdentif-er object return via EPMDocument with getIterationInfo() information. See since iterations are also series based or can be configured to be, my guess is its a varchar and orderby is using wtintrospection to treat it as a string such that orderby is working correctly and you cannot change this. Some place out there may want 1.A and 1.B and the system seems capable of supporting it. There are unsupported and likely undocumented means to accomplish this still via queryspec via inserting Oracle or SQL Server SQL - whichever applies. I have used them.

So I guess to help you, can you produce the SQL equivalent first so a string to int function can be used. That may violate wtintrospection though, worth a try though. I'd have to dig up some append SQL logic. Course your not supposed to decompile code, so yeah. May have to take this one offline if the above sounds interesting.

Another way to do this might be to use latest iteration info and order based on this being your top level result.





Sent from my Verizon Wireless BlackBerry

[RandyJones]

On 5/8/2012 6:19 PM, David DeMay wrote:
> What does the IterationIdentif-er object return via EPMDocument with getIterationInfo() information.
>   See since iterations are also series based or can be configured to be, my guess is its a varchar and orderby is using wtintrospection to treat it as a string such that orderby is working correctly and you cannot change this. Some place out there may want 1.A and 1.B and the system seems capable of supporting it.  There are unsupported and likely undocumented means to accomplish this still via queryspec via inserting Oracle or SQL Server SQL - whichever applies. I have used them.

I would be interested in knowing how to apply my own sql so I know what
I am getting as opposed to the black box mostly undocumented QuerySpec
and other ungrokable related Windchill api querying functions. Quoting
from an Alcon employee who took the same API class as I. "Nothing like
taking an elegant sql query and *&^%*&^%*&% it with QuerySpec"
Even after using QuerySpec for a few projects I still feel like I must
be missing something obvious. Every time I use QuerySpec it takes 2 or 3
hours to figure out  how to do what I would consider trivial sql queries.

>
> So I guess to help you, can you produce the SQL equivalent first so a string to int function can be used.

A functional oracle query is this:
select
     edm.documentnumber,
     ed.versionida2versioninfo,
     ed.iterationida2iterationinfo
from
     epmdocumentmaster edm,
     epmdocument ed
where
     edm.documentnumber like 'someNumberHere'
     and ed.ida3masterreference = edm.ida2a2
     and ed.statecheckoutinfo != 'wrk'
order by
     edm.documentnumber,
     ed.versionsortida2versioninfo desc,
     cast(ed.iterationida2iterationinfo as int) desc;

This casts the epmdocument.iterationida2iterationinfo as an integer and
then orders it like I am wanting in this case.

>   That may violate wtintrospection though, worth a try though. I'd have to dig up some append SQL logic. Course your not supposed to decompile code, so yeah. May have to take this one offline if the above sounds interesting.
>
> Another way to do this might be to use latest iteration info and order based on this being your top level result.

The problem is that the latest iteration info is only good for sorting
the latest iteration. It won't do any good for sorting the other non
latest iterations.

>
>
>
>
>
> Sent from my Verizon Wireless BlackBerry
>
> -----Original Message-----
> From: Randy Jones<->
> Date: Tue, 08 May 2012 17:54:52
> To: David DeMay<->
> Cc: Solutions PTCuser<->
> Subject: Re: [solutions] - RE: QuerySpec sort EPMDocument iteration as an
>   integer vs as text
>
> On 05/08/12 16:33, David DeMay wrote:
>> Sounds like a bug with using OrderBy?
> No I wouldn't call it a bug with OrderBy because the iterationida2iterationinfo
> column is defined as a varchar2 column in the database so technically it is
> sorting correct (as text). If I was creating a query for a mysql table I would
> simply do this:
> ...
> order by cast(`iterationida2iterationinfo` as SIGNED)
>
> So I would coerce mysql into treating the field as an integer and let the
> database do all the work of sorting because:
> 1. the query is simple
> 2. databases are proficient at sorting (generally much faster than you can do it
> in some program code)
>
> So the question is how to use QuerySpec and or some undocumented OrderBy magic
> to sort a particular attribute as an integer?
>
>
>> I never use it and instead build java comparator classes for lists of results or input.
>>
>>
>>
>>
>> Sent from my Verizon Wireless BlackBerry
>>
>> -----Original Message-----
>> From: Randy Jones<->
>> Date: Tue, 08 May 2012 16:03:48
>> To:<->
>> Reply-To: Randy Jones<->
>> Subject: [solutions] - QuerySpec sort EPMDocument iteration as an integer vs
>>    as text
>>
>> Windchill 9.1 M050
>>
>> I have the following QuerySpec:
>>
>> String epmNo = "someEPMDocumentNumber";
>> QuerySpec qs = new QuerySpec(EPMDocument.class);
>> ClassAttribute number = new ClassAttribute(EPMDocument.class, EPMDocument.NUMBER);
>> ClassAttribute versionSort = new ClassAttribute(EPMDocument.class,
>> "versionInfo.identifier.versionSortId");
>> ClassAttribute iteration = new ClassAttribute(EPMDocument.class,
>> "iterationInfo.identifier.iterationId");
>> qs.appendWhere(new SearchCondition(EPMDocument.class, EPMDocument.NUMBER,
>> SearchCondition.LIKE, epmNo), null);
>> qs.appendOrderBy(new OrderBy(number, true));
>> qs.appendOrderBy(new OrderBy(versionSort, true));
>> qs.appendOrderBy(new OrderBy(iteration, true));
>> StatementSpec statementSpec = (StatementSpec) qs;
>> qr = PersistenceHelper.manager.find(statementSpec);
>>
>> Where I am searching for "someEPMDocumentNumber" and then sorting by the number,
>> version (versionSortId), and the iteration. This is working like I want except
>> for the iteration part. This is getting sorted like text so for example on an
>> revision with 12 iterations I am getting results like this:
>> Rev.Iteration
>> A.9
>> A.8
>> A.7
>> A.6
>> A.5
>> A.4
>> A.3
>> A.2
>> A.12
>> A.11
>> A.10
>> A.1
>>
>> When I want it to sort like this:
>> A.12
>> A.11
>> A.10
>> A.9
>> A.8
>> A.7
>> A.6
>> A.5
>> A.4
>> A.3
>> A.2
>> A.1
>>
>> So how do I define the query to sort the iteration as an integer?
>>
>


--

[shudh]

Hi Randy,

I guess the desirable result could be obtained by post processing the
collection of returned objects through your own sorting method..

Comparator or a simple method to compare ints after splitting at dots...

But I think the bigger problem which Windchill architecture is addressing
is to minimize, if not remove, impedance mismatch between object layer and
relational layer ..using an ORM.. the ObjectMappable .. However the object
query languages may not be so natural to enterprise programmers who are
used to SQL .. I found JPA to be easy to start with but then while using
JDOQL using Google appengines cloud datanucleus adaptor it got messier. But
I guess web had adapted well from relational stores to object stores..with
massive scalability.

I have always found Windchill ' s architecture to be best in class and
ready to evolve..

Best Regards
Shudh

——--------
Written on Droid

[ddemay]

I replied back to Randy privately that when you combine latest iteration with soft order of object identifer I'd -and/or- sort order of create date timestamp you might get it to work.


Sent from my Verizon Wireless BlackBerry

[RandyJones]

My workaround to the limitations of OrderBy is to use the checked in timestamp instead of the
iteration for ordering. So my QuerySpec code becomes this:

String epmNo = "someEPMDocumentNumber";
QuerySpec qs = new QuerySpec(EPMDocument.class);

ClassAttribute number = new ClassAttribute(EPMDocument.class, EPMDocument.NUMBER);
ClassAttribute versionSort = new ClassAttribute(EPMDocument.class,
"versionInfo.identifier.versionSortId");
//ClassAttribute iteration = new ClassAttribute(EPMDocument.class,
"iterationInfo.identifier.iterationId");
ClassAttribute checkedInOn = new ClassAttribute(EPMDocument.class, "thePersistInfo.modifyStamp");

qs.appendWhere(new SearchCondition(EPMDocument.class, EPMDocument.NUMBER, SearchCondition.LIKE,
epmNo), null);
qs.appendOrderBy(new OrderBy(number, true));
qs.appendOrderBy(new OrderBy(versionSort, true));
//qs.appendOrderBy(new OrderBy(iteration, true));
qs.appendOrderBy(new OrderBy(checkedInOn, true));

StatementSpec statementSpec = (StatementSpec) qs;
qr = PersistenceHelper.manager.find(statementSpec);


Notice I am now ordering by thePersistInfo.modifyStamp instead of
iterationInfo.identifier.iterationId. I want to thank Dave and Shudh for their assistance.

[Avinash]

use the below api to achieve the same result,

wt.vc.VersionControlHelper.service.allIterationsOf(Mastered master)

It return the QueryResult and finds all of the iterations of the given master. The result is an ordered list of iterations from the most recent one to the first one created for that version, e.g. (B.2, B.1, A.3, A.2, A.1).

Regards,
Avinash

[vuchekar]

Hi Avinash,

please can we provide some more info on how to use above mentioned API.

It will be grate if you provide some example on this API.

Thanks,

Vivek

[BhushanNehe]

Vivek,

Check if the following example helps:

EPMDocument target=null; // get your cad document handle
  QueryResult qr = VersionControlHelper.service.allIterationsOf((EPMDocumentMaster)target);
  while (qr.hasMoreElements()) {
  EPMDocument iteration = (EPMDocument) qr.nextElement();
  }

Regards,

Bhushan