cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

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

avillanueva
Alexandrite

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

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.

4 REPLIES 4

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

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?

 

 

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

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.

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

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

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

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

 

Announcements