How to define QuerySpec for latest rev, iteration and UDA = some value

[RandyJones]

I am attempting to create a QuerySpec that will search for the latest
revision, latest iteration, of all EPMDocuments that have a user defined
attribute RELEASE_NUMBER = to some value. I have found code snippets here
in this forum that is getting me close. What I have so far is this:

String MyReleaseNumber = "2010-426";
QuerySpec qs = new QuerySpec(EPMDocument.class);
int defindex = qs.addClassList(StringDefinition.class,false);
int valueindex = qs.addClassList(StringValue.class,true);
qs.appendWhere(new SearchCondition(StringDefinition.class, StringDefinition.NAME, SearchCondition.EQUAL, "RELEASE_NUMBER"),defindex );
qs.appendAnd();
qs.appendWhere(new SearchCondition(StringValue.class, StringValue.VALUE, SearchCondition.EQUAL, MyReleaseNumber),valueindex );
qs.appendAnd();
qs.appendWhere(new SearchCondition(StringDefinition.class, "thePersistInfo.theObjectIdentifier.id", StringValue.class, "definitionReference.key.id"), new int[] { defindex, valueindex });
qs.appendAnd();
qs.appendWhere(new SearchCondition(EPMDocument.class, "iterationInfo.latest", "TRUE"));
System.out.println("Query String:\n" + qs.toString());

What this gives is the following query string:
SELECT A0.*,A2.*
FROM wt.epm.EPMDocument A0,wt.iba.definition.StringDefinition A1,wt.iba.value.StringValue A2
WHERE (A1.name = 'RELEASE_NUMBER') AND (A2.value = '2010-426') AND (A1.idA2A2 = A2.idA3A6) AND (A0.latestiterationInfo = 1)

What I am lacking is 2 parts.
1. The part that joins StringDefinition and EPMDocument. This should look like
    "and (A2.IDA3A4 = A0.IDA2A2)"  How do I get the magic combination of appendWhere
     and SearchCondition to give this simple condition???

2. The part that returns only the latest revision. I am not even sure what this
    part of the query should look like.


Does anybody have any pointers or helpful hints on this?

Attached is a screenshot of a very similar query I have defined in the advanced
search page.

Thanks
--

[MikeLockwood]

Almost hopeless to do so directly in the database; should use query builder, not sql.

Getting Query Builder to filter on latest Iteration (constant=1) is straight forward.
Getting Query Builder to filter on latest Revision though is tricky - need to use subselect and use max(Revisions).

Sample qml attached that uses last Rev (import to Report Builder). I put in 6 different tech support calls to find out how to do this and finally got someone who knew - there should be but isn't an example of it query builder Help.

Given this base, simply add the attribute of interest to the criteria and make it a parameter, and expose the report to users.

[LiuLiang]

I would like to see if anyone has a query builder reportthat reports the latest rev with user defined attributes (IBAs).

I can either report the latest iteration on the latest rev without any IBA columns or latest iteration on every revision with IBA columns.

When I do both (latest rev + IBAs), query builder gives me error.

I have tried this on Windchill 8.0, 9.0 and 9.1 in the past and no luck of success so far.

If anyone can point me to a correct query builder report example that does this, we can use query builder to quickly build our reports instead of going through other routes.

Thank you very much.

Liu

In Reply to Mike Lockwood:

Almost hopeless to do so directly in the database; should use query builder, not sql.

Getting Query Builder to filter on latest Iteration (constant=1) is straight forward.
Getting Query Builder to filter on latest Revision though is tricky - need to use subselect and use max(Revisions).

Sample qml attached that uses last Rev (import to Report Builder). I put in 6 different tech support calls to find out how to do this and finally got someone who knew - there should be but isn't an example of it query builder Help.

Given this base, simply add the attribute of interest to the criteria and make it a parameter, and expose the report to users.

[RandyJones]

On 10/05/10 10:10, Liu Liang wrote:
> I would like to see if anyone has a query builder report that reports the latest
> rev with user defined attributes (IBAs).

See attached for an example that reports epmdocuments like the following:
latest iteration
latest revision
release_number = some value  (this is a user defined attribute)

>
> I can either report the latest iteration on the latest rev without any IBA
> columns or latest iteration on every revision with IBA columns.
>
> When I do both (latest rev + IBAs), query builder gives me error.
>
> I have tried this on Windchill 8.0, 9.0 and 9.1 in the past and no luck of
> success so far.
>
> If anyone can point me to a correct query builder report example that does this,
> we can use query builder to quickly build our reports instead of going through
> other routes.
>
> Thank you very much.
>
> Liu
>
>



--