View Source

Normally to create a fully functioning Dashboard we will need to reference more than one table/view and the table join(s) will need to be defined in the dashboard.

The screenshot below shows several different tables and the joins, indicated by the blue lines, between them.

pi Documentation > Table Joins > image-20210223-083649.png

Creating a Join

To create a join, click onto the header of the table we want to join from and click on the ‘Joins’ tab, this time however click on the ‘Add’ icon to create a new join.

pi Documentation > Table Joins > image-20210223-083752.png

You will then be presented with a list of the tables/ views already configured in this data connection. Click the one you want to join to and the line for this join is created.

pi Documentation > Table Joins > image-20210223-083831.png

Clicking on the newly created line at the top of the screen will display the columns in each table.

pi Documentation > Table Joins > image-20210223-083857.png

At this stage, you could manually type in the join in the ‘result’ window. Normally however, even if the join is more complex than a simple ‘equal to’, it is better to select the field(s) from each table using the two lists on the left-hand side, which will ensure that the correct aliases and object quotes are pulled through. After selecting the two fields, click the ‘Append to SQL’ button.

pi Documentation > Table Joins > image-20210223-083937.png

The join will be created and displayed in the Result area.

pi Documentation > Table Joins > image-20210223-084128.png

For more complicated joins you can click on multiple objects before pressing the ‘Append to SQL’ button. You can also manually edit the SQL generated, any SQL syntax that is valid for your database type such as hints, substrings, cast etc. can be used.

Testing a Join

After the join has been built it can be tested using the ‘Test join SQL’ icon in the Result area which will return sample data from both tables.

pi Documentation > Table Joins > image-20210223-084153.png

Clicking the ‘SQL’ tab will display the SQL executed to get this result set.

pi Documentation > Table Joins > image-20210223-084241.png

Deleting a Join

To delete an existing join, click on the header of a table (the grey bar at the top of the table that shows the name of the table).

pi Documentation > Table Joins > image-20210223-085420.png

This will show the following window. To view existing joins, click on the ‘Joins’ tab. In the following example, we can see that the postcode field in the ukpostcodes table is joined to the CUS_POSTCODE field in the nr_customers table.

pi Documentation > Table Joins > image-20210223-085457.png

If we then click the ‘To Delete’ button at the bottom left of the window

pi Documentation > Table Joins > image-20210223-085537.png

the screen will change, and a tick box will be displayed at the end of the join row. To delete this join, select the tick box and then press ‘Delete Selected’.

pi Documentation > Table Joins > image-20210223-085623.png

Having ‘orphaned’ tables is generally a bad idea. Where stand-alone tables are required, they should ideally be placed in a separate data connection(s) specifically for such purposes.