Hi Automate Community!
The tip this week is for Studio Query. When you create Query scripts to pull live data from SAP, every table must have at least one join to another table (when using more than one table). Joins are automatically created on primary keys. If there are no primary keys, then Query automatically creates joins on index keys.
Joins can be removed (right click on the join and choose "Remove"), converted to a left outer join (right click on the join and choose "Convert to left outer join"), or added manually (simply drag from the field in one table to the field in the other table). You can also manually add joins on fields with different character lengths. While it's recommended to have joins on primary keys or index keys, you can have joins on non-indexed fields as well. This might cause the Query script to run more slowly, but sometimes it's necessary to get the SAP data you need.
If you want to see what joins are available between tables, click "Find Joining Table" in the ribbon. Choose the two tables you want to see joins between and you will see a list of joins below. If the box is checked on the left, then the join already exists for those fields. If you want to add a join that hasn't been applied, check the box on the left and click "Apply Selected Joins" at the bottom.
If you run into a scenario where you know you need a table but you don't see any joins with the tables in your Query, then click "Find Joining Table" in the ribbon. From there, select the two tables you need to join and click "Intermediate Joins". This will search for other tables to join your two tables together.
If you're new to joins look at these visuals to better understand what joins are and what the join options are.
Inner join:
Left outer join:
Right outer join:
Right outer joins are not an option in Query, so if a right outer join is needed, simply switch the tables in the Query and choose left outer join instead. Please note that you are limited to one left outer join per Query script.
A quick reminder that custom tables and fields can be used in the Query scripts as well.
Here is documentation on creating joins between tables in Query.
Feel free to share any interesting or complex Query scripts you have created!
Thanks!
------------------------------
Maria Ebner
*Precisely Software Inc.
------------------------------