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...

Highlighted
Aquamarine

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.