QUERY error for BY column

Question

I'm using the following query to pull data from one sheet into another.

=query(data!B2:CL,"select B, C, D, E, F, G, H, I, J, BB, BZ, CA, BA where J = 'Industry Certification'",false)

The above query works perfectly. However, if I change 'BB' to 'BY'. I get the following error.

Error

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered "BY" at line 1, column 35. Was expecting one of: "true" ... "false" ... "date" ... "timeofday" ... "datetime" ... "timestamp" ... "min" ... "max" ... "avg" ... "count" ... "sum" ... "no_values" ... "no_format" ... "is" ... "null" ... "year" ... "month" ... "day" ... "hour" ... "minute" ... "second" ... "millisecond" ... "with" ... "contains" ... "starts" ... "ends" ... "matches" ... "like" ... "now" ... "dateDiff" ... "quarter" ... "lower" ... "upper" ... "dayOfWeek" ... "toDate" ... ... ... ... ... ... "(" ... "-" ...

The value of cell 'BY' is a simple string (activities).

I'm scratching my head confused. Any help is greatly appreciated.

Answer 1

Short answer

According to the documentation, BY is a reserved word, to use it as an identifier, it should be back-quoted.

Explanation

I think that it's a reserved word because it's used by two clauses:

• Order By
• Group By

Answer 2

use ` instead of ' like:

=QUERY(data!B2:CL, "select B, C, D, E, F, G, H, I, J, `BY`, BZ, CA, BA 
                    where J = 'Industry Certification'", 0)

Answer 3

Although the answer provided by user0 is the best solution for this, I also wanted to add an alternative workaround for this.

You can use an Array Formula to duplicate the entire "BY" column in a different column and use that duplicated column in the Query formula. Check out the following G-Sheet which shows how this workaround can be implemented (it also has the solution)

Screenshot from the G-Sheet below: