TIL two things about union queries in Query Builder I thought I'd share...

[avillanueva]

Query Builder is not the worst tool ever but if they only documented the limitations, it would save me hours of time. I've been working with union queries recently to get around some of the limitations (10.2) of the UI when dealing with queries across the 3 main data types (Parts, Docs and CAD Docs).  For all those who do not know, do make a union, you need to create your queries separately in Query Builder and export them to a .qml file.  This is an XML file that contains the query definition.  You will see some header stuff but your query will be wrapped in a "<Statement>" clause. Create a new qml file with this template:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE qml SYSTEM "/wt/query/qml/qml.dtd">
<qml bypassAccessControl="true" legacyMode="false">
   <statement>
       <compound type="union">

      </compound>
   </statement>
</qml>

Take your <Statement> blocks from your other .qml queries and paste them in, save and import back to Query Builder.  

What I learned in this process:

1. When I imported and ran my query, I kept seeing no results and the yellow error icon at the bottom with this message:

ORA-00932: inconsistent datatypes: expected - got BLOB

 These messages scare the crap out of me b/c i fear DB integrity. It was as simple change I made to turn of bypassing of access rights: <qml bypassAccessControl="false". I wanted to make sure results were only what the person running it could see but this cause the error I saw. I set it back to true and it was fine.  Phew!

2. I also saw this message on import when I ran my union query:

wt.query.QueryException: Attribute "name" is not a member of class "class wt.team.RolePrincipalMap"

No where in my query did I call out "name" for this object. It made no sense as to why this was coming up. It was caused by this block:

<orderBy>
         <orderByItem>
               <columnTarget heading="Product/Library"/>
          </orderByItem>
          <orderByItem>
               <columnTarget heading="Role"/>
          </orderByItem>
</orderBy>

Don't ask me how I guessed that but I remember something like this when I was writing SQL where it didn't like ordering inside a union. I had this in my query from Query Builder and I just needed to remove that block from the .qml file.  I hope these help when working with unions.

[cmhaka]

Hello,

I discovered this post while searching to see if there's a way to join the results of 2 reports I created. So I exported the qml for each report as you mentioned and tried a join. I do not know if it worked because when I went to upload the new qml I got the message "the file you are attempting to import is invalid. only reports exported from windchill can be imported". Is there a trick to getting the modified qml file uploaded?

[avillanueva]

I just ran into the same message and reported to PTC. I was using 10.2 M030 previously and the QML Structure as changed. We are now on 11.1 M020. This structure worked:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<qml … (rest of the standard stuff here)>

<compound type="union">

</compound>
</qml>

In between the compound tags, copy in the <query></query> blocks you export from each of the qml exports.  Same rule applies for bypassAccessControl=true. Because of this, you have to take note of CS292726 . You need to grant access to run this report for non-site admins via a group.

[cmhaka]

Thank you for the info! We are using 11.1 as well. I will have to revisit and give this another try. 

[Michele_Babolin]

hi,

thanks for sharing!

I tested it but I think I am missing something. Do you confirm the structure must be:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<qml … (rest of the standard stuff here)>

<compound type="union">

    <query>

         (query1)

    </query>

    <query>

        (query2)

    </query>

</compound>
</qml>

In my test query1 and query2 use the same objects/tables with different links (query1 legacy change links, query 2 flexible change links) and have the same select.

What it happens is that the input criterion is applied only to the query1 (which thing is clear by retrieving the "View Modified QML")

[rleir]

Hi team

This is a late reply and maybe the issue is old history. 

I was also hoping to do a union, but never found an acceptable way to do it. Now I see there is a Changeable type representing Docment, EPMDocument, and Part. Do your join like this:

  Change Task (WTChangeActivity2)

       V

  Affected Activity Data

        V

  Changeable (wt.change2.Changeable2)

The latter has a Type field telling you whether it's an EPMDocument or something else. The identity field gives you the name and version. You can also join to the Doc or EPM type, but that defeats the purpose.

cheers -- Rick   

[rleir]

It's also possible to link from the changeable to the doc or part using outer joins:

             Change Task
                   V
             Change record
                   V
               changeable 
              V         V
control branch        control branch
   V (outer)              V (outer)
Document                  part

Now you can report on the part's state and the doc's state, which was not available otherwise.  Unfortunately, they cannot be in the same column of your report (please correct!)

@avillanueva @Marco_Tosin @HelesicPetr   

[rleir]

My mistake, the relation is:

            Change Task
            V          V
  Change Record      Change Record
        V (outer)          V (outer)
    Document              Part

Then if you 'Select or Constrain' the Name and Number for both Document and Part, then for each object row, either the Part fields or the Doc fields will be blank.

[avillanueva]

Still works in 12.0. Also a note that if you want to export a union report as a Report, you cannot edit it so this is a DB update. There is a column in the Report Template table. Just need to flip exportasReport to 1 via SQL update.  On the Reports page, you have to click Refresh Reports List to see it. That's not 3 things I learned.