If you feel more comfortable creating SQL queries from scratch, you can do so by using SQL Mode in your Query Action. If you added columns or filters in Visual Mode then switched to SQL Mode, you’ll see the same auto-generated SQL query that also appears in the SQL preview in Visual Mode’s Advanced Drawer. The key difference here is you can edit that SQL query to include more complex SQL functions that may not be feasible to do via Visual Mode.
If you need to reference the tables and columns in your data source, we’ve provided a sidebar version of Visual Mode’s schema browser on the left side of the SQL Mode editor.
Tip! Building queries in Visual Mode then switching to SQL Mode is a great way to generate the basic structure of return values and even joins, before editing the query with more specific needs.
The SQL syntax used for your Query in SQL Mode must be the syntax for your data source’s underlying database type. However, the SQL syntax in all other Actions is SQLite.
Note: CSVs will use PostgreSQL syntax.
Keyboard shortcuts! Use
Command + Shift + F to nicely format your SQL query.
As you type, the convenient autocomplete feature shows basic SQL keywords, tables, and columns available for you to use in your SQL query.
SQL Mode schema browser
By default, SQL Mode shows the schema browser pane on the left side of the Query. Just like the Visual Mode schema browser, you can see and search through all the schemas, tables, and columns available from your data source. Clicking on a column from the schema browser adds the column name to your SQL query.
Controls and Variables browser
Toggle to the Controls and Variables browser to see any available Dashboard Controls and all the Relative Date Variables you can use in your SQL query. Similar to the schema browser, clicking on a Dashboard Control or Relative Date Variable from the list adds the Control or Variable to your SQL query, including the special Chartio-specific syntax you need to properly reference them.
You can create more dynamic date options using Chartio’s datetime functions on date Controls and Relative Date Variables.
Edit Control values
If you’re referencing Dashboard Controls in your query, you can click the Pencil icon at the upper-right corner of the Controls and Variables browser to open the Edit Variable Values modal. From this modal, you can change the values of your Dashboard Controls without having to exit Visual SQL. Upon clicking Ok, your query automatically re-executes to reflect the new Control values. To set your Control values back to the default values, click Reset Values in the bottom-left corner of the modal.
Resize SQL editor
If the normal view of the SQL Mode editor seems a bit cramped, you can expand the SQL editor to give yourself more room to write your SQL query. Scroll to the bottom of the Query and click the Maximize icon at the bottom-left corner. This hides the chart preview to make more space for creating your SQL query.
To show the chart preview again, you’ll need to switch back to the Normal view by clicking the Normal icon (same location as the Maximize icon) or collapsing the Query.