Skip to main content
avillanueva
23-Emerald I
avillanueva23-Emerald I
23-Emerald I
September 13, 2019
Question

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

  • September 13, 2019
  • 1 reply
  • 4996 views

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.

    1 reply

    C
    cmhaka9-Granite
    9-Granite
    March 6, 2020

    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
    23-Emerald I
    avillanueva23-Emerald IAuthor
    23-Emerald I
    June 19, 2020

    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.

    C
    cmhaka9-Granite
    9-Granite
    August 25, 2020

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

    Terms & ConditionsAccessibility statement