Knowledge base

Query Editor

Sometimes you have a quick question of your data that doesn't really warrant opening up Excel or your favourite reporting tool. This is where our Query Editor comes in! > Got a little more time? We have a [detailed blog post here]( ### How it works It's pretty simple really - our Query Editor uses the _read-only_ credentials provided by SyncHub to execute your SQL directly against your data warehouse. Afterwards, you can **save and share** the result with other collaborators in your organisation. You can do some pretty awesome and powerful things with your Query Editor. Here are some of the features and tools to help you become a master. ### Data model To aid you with constructing your queries in the query editor, we provide a view of the data model for each of your connections so that you may see what tables and columns are available for each of the connectors. From the tools menu in the top right of the query editor, click **show endpoints**. You will then get the following expandable view listing each connector with its available tables and columns. You can click on the blue arrows to automatically insert the column or table name into your query at the location of the cursor. ![]( From the data model popup, you can also open the sync window for each of the endpoints, allowing you to manually re-sync data for specific end points without needing to leave the query editor. ![]( ### Handling multiple connections to the same cloud platform To make your queries easier to read, you can use the **CONNECTIONS** placeholder in place of the actual database name -for example, _[CONNECTIONS.xero]_ would connect to your Xero schema. If, however, you have two or more of the same type of connector, using this ambiguous term will cause an error. There are two ways to report across multiple connections of the same type: #### Solution 1/2 - You only need to reference one of the duplicate connections In this case, you can simply use the data model to **remove from query** the connections that you don't want to use. You will see it becomes greyed out, and now using **CONNECTIONS.harvest** will only reference the connection that is still active for this query. ![]( #### Solution 2/2 - You need to be able to reference more than connection to the same cloud platform in the same query To support this, you can click on the blue arrow next to a table name to insert that table _with its specific schema_, as shown below. This allows you to, for example, gather all of you invoices from two different Harvest accounts and view them in a single query. ![]( ### Show calculated SQL Another option from the tools menu is **show calculated SQL**. Clicking this will open a window showing the exact SQL that you query will be executed as. This is particularly useful to be able to see how your parameters and **embedded queries** are actually being run in the context of the query you are currently editing. A button to view the calculated SQL will also appear when a query encounters an error when run. For example, executing the following query gives an error, and when viewing the calculated SQL, it becomes clear that this is a malformed SQL query. ![]( ![]( You would then see that the correct way to form this query utilising your embedded query would be as so: ![]( ![]( ### Parameters Setting up a query as a parameter provides very useful functionality for viewing data relating to different items of the same entity. Here are some examples of useful parameter queries that show how they can be used: - Lists countries - Label: Country name - Value: Country primary key/ID (could also be the country name if the database just uses the country name instead of foreign keys to a Country table) - Use case: In a database that stores multiple tables of information about different countries, use this paramter to view the entries specific countries, one at a time, without needing to remember the exact spelling or IDs of each country as you can select one from the parameter drop down - List invoices created in the past 30 days - Label: Invoice name or number, can concatenate with the date of creation for more informative labelling - Value: The ID of the invoice - Use case: You can create a query to view all the time entries for a specific invoice, and instead of having to copy and paste specific invoice IDs, you can select the invoice by name from the parameter drop down ![]( In the select statement of a query that you would like to use as a parameter, you must include the column that you would like to use as the labels for the parameter, and the column you would like to use as the values (more on this below). To set up a query as a parameter, select the **use as parameters** option under dynamic parameters in the query settings. You will need to set some of the parameter settings: - Labels: which column should be displayed to the user? - When using this parameter in another query, you will be able to select from a drop-down list which value you would like to use for the parameter based on the label column - Values: Which column should be injected into the destination query? - When using this parameter in another query, the corresponding value for the label that you select from the drop-down will be inserted into the query at the location that the parameter marker appears. - Default value (optional): What value should we use if the user doesn't select from the drop-down list - Whilst this is an optional setting, it is recommended to set it where possible. If there is no value selected from the drop-down list, the default value will be used at the location of the parameter marker. A common value for the default could be -1 if the number of the value column you selected begins at 0, as this way you know there will be no results if no value is selected. If there is no default set and you execute a query without selecting a value for a parameter, you calculated SQL will likely be malformed due to a gap where the parameter value _would have_ been placed. ![](