Automate

​I have been working with Transaction but am new to query.  I want to use it to Audit data.  I am trying to set up an exception join does anyone know how you would do something like this?

R.

------------------------------
Randall Tupper | senior project analyst
Kohler Company | [City] | 9206987335
------------------------------

For reference ​I use Studio v11, I'm wondering if you're referring to a 'left outer join'? In the version I have there's only two different kinds, Inner and Left Outer Join.

If that's the case then once you've got the initial join done between your tables, simply right click the join and select 'convert to left outer join'. Alternatively there's an option on the ribbon that does the same thing.



------------------------------
Douglas Cripps |
Welsh Water | [City] |
------------------------------

Thanks for the help, Douglas!

We also are hosting an upcoming WUGinar featuring Query on October 11th. Stay tuned for more information and details on this educational training through our website.

------------------------------
Community Manager | Winshuttle
------------------------------

Original Message:
Sent: 09-13-2018 11:22
From: Douglas Cripps
Subject: Winshuttle Query

For reference ​I use Studio v11, I'm wondering if you're referring to a 'left outer join'? In the version I have there's only two different kinds, Inner and Left Outer Join.

If that's the case then once you've got the initial join done between your tables, simply right click the join and select 'convert to left outer join'. Alternatively there's an option on the ribbon that does the same thing.



------------------------------
Douglas Cripps |
Welsh Water | [City] |
------------------------------

​Well an exception join is a little different but an pouter join might work.  Thanks for the feedback Doug

------------------------------
Randall Tupper | senior project analyst
Kohler Company | [City] | 9206987335
------------------------------

Original Message:
Sent: 09-13-2018 17:38
From: Community Manager
Subject: Winshuttle Query

Thanks for the help, Douglas!

We also are hosting an upcoming WUGinar featuring Query on October 11th. Stay tuned for more information and details on this educational training through our website.

------------------------------
Community Manager | Winshuttle
------------------------------


Original Message:
Sent: 09-13-2018 11:22
From: Douglas Cripps
Subject: Winshuttle Query

For reference ​I use Studio v11, I'm wondering if you're referring to a 'left outer join'? In the version I have there's only two different kinds, Inner and Left Outer Join.

If that's the case then once you've got the initial join done between your tables, simply right click the join and select 'convert to left outer join'. Alternatively there's an option on the ribbon that does the same thing.



------------------------------
Douglas Cripps |
Welsh Water | [City] |

​Something to note, the ability to do left outer joins is controlled by "developer proficiency level" in Central (v10.7) or Foundation (v11).  If you are not seeing the ability to do a left outer join, it may be due to the fact that 'enable left outer joins' is not active for the developer proficiency level that is assigned to your user.

------------------------------
Jeremie Dippel [Designation]
Project Engineer @ Rockwell Automation
Wisconsin Local Wug Leader
I'm your huckleberry.
------------------------------

Original Message:
Sent: 09-13-2018 17:38
From: Community Manager
Subject: Winshuttle Query

Thanks for the help, Douglas!

We also are hosting an upcoming WUGinar featuring Query on October 11th. Stay tuned for more information and details on this educational training through our website.

------------------------------
Community Manager | Winshuttle
------------------------------


Original Message:
Sent: 09-13-2018 11:22
From: Douglas Cripps
Subject: Winshuttle Query

For reference ​I use Studio v11, I'm wondering if you're referring to a 'left outer join'? In the version I have there's only two different kinds, Inner and Left Outer Join.

If that's the case then once you've got the initial join done between your tables, simply right click the join and select 'convert to left outer join'. Alternatively there's an option on the ribbon that does the same thing.



------------------------------
Douglas Cripps |
Welsh Water | [City] |

​Exclusion joins are not supported. I recently had a need to create BOM extracts and needed to exclude those that had been changed with Change Numbers active.  I finally ended up doing 2 reports and using filters to remove the items that I did not want.  It worked but having an exclusion join would have been cleaner.

------------------------------
Greg Papendick | Director, Master Data Management
Dairy Farmers of America, Inc.
------------------------------

Hi All - we don't really have exception joins, where you want everything in one table that doesn't have a match in the 2nd table. 

However what is new in v12, an outer join that allows conditions on the table with the outer join.  Example:  MARA (materials) can be joined with a left outer join to MAKT (descriptions).  We could do this with all versions of Query, however we couldn't use a condition on any field in the table with an outer join - in this case MAKT. 

In v12, you can do so.  So you could do something like, list all material numbers and description, that have a German description (SPRAS=DE).   With the outer join, you can see all materials, and if there is a German description, it will list it.  But you'll also be able to see all materials that don't have a German description in the output.

Hope this is helpful.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle North America
------------------------------