Grow gives you the ability to directly query your data with SQL. Unfortunately this means that errors sometimes crop up. Here are some common errors and solutions that you might run into.
SQL Syntax Error near "your code here"
Solution: SQL is a strict language, which means that if there is 1 error, the whole thing errors out. SQL will attempt to tell you where the error is. It does this by telling you where the error is 'near'. Fixing the syntax error will fix this error.
SQL Syntax Error. no such column
Solution: Sometimes SQL will give you an error that a column doesn't exist, when you know that column does exist. If there are spaces or non-alphanumeric (not A-Z and 0-9) characters in your column headers, you have to use double quotes or backticks to tell SQL it is a column header name.
Here is an example: I have a data source or file that has the column name of Weird Header. I want to select that column in SQL, so I use the following SQL:
SELECT Weird Header FROM report1; and get the error:
SQL Syntax Error. no such column, Weird .
This is because I have a space in my header name. So I have to use double quotes to tell SQL that "Weird Header" is the column name. Changing the SQL to this works great:
SELECT "Weird Header" FROM report1;
Another example: Recurly gives back column headers that include dots or periods like this:
SQL will give the same error because the
. confuses it into thinking you are selecting part of a table. Use double quotes to tell SQL that "Revenue.Month" is a real column name and exists.
There is also another
no such column error that is harder to find. Spaces in fields at the end of words can cause more trouble.
If you have a column name that has a space after the last word, you need to have a space after the last word in the SQL as well. For example:
STRFTIME('%Y-%m-%d',DATE(Timestamp, 'weekday 5')) as week,
`Who are you` AS name,
Could return the error
SQL Syntax Error. no such column, Who are you?
This doesn't make sense until you look at the Excel file, and see that the column name is actually 'Who are you? '
Notice the space after the
? . That space is causing the error.
So if you rewrite SQL with a space after
? , the query would look like this:
STRFTIME('%Y-%m-%d',DATE(Timestamp,'weekday 5')) as week,
`Who are you? ` AS name,
This will fix the error.
SQL Error (report1): Table data is malformed
Solution: When creating a Master Report, the referenced reports need to have all of their columns named. Otherwise the SQL wouldn't be able to know which columns to pull in.
You can either change how the data source is getting pulled in, or you can also check 'Add column headers' under Advanced data settings.
SQL Syntax Error. RIGHT and FULL OUTER JOINs are not currently supported by SQLite
Solution: Grow uses SQLite, and the SQLite team has chosen to not implement a few common SQL actions. See the list here. You can get around this by using a different combination of joins. For example you can replicate a FULL JOIN by doing a 2 left joins and a union all.