Sometimes, you have to use subqueries!

Query without FROM clause cannot have a WHERE clause, goes the old SQL adage.

So I had this interesting problem the other day. Let's say an order has three boolean flags, each indicating whether a particular error has occurred during its lifetime. Our task is to create an array of all the errors that occurred for each order.

In order to solve it, we:
- create a scalar subquery
- since the flags can have the NULL value, we'd need to filter them out before passing them to the arrays constructor (which doesn't like nulls)
- create the array using the ARRAY () constructor

Found it useful? Subscribe to my Analytics newsletter at https://www.notjustsql.com.

---

Enjoyed this? Here are some related articles you might find useful:

• Beware of ROW_NUMBER without ORDER BY
• Tidying up WINDOW functions in BigQuery with named windows
• Using RANGE in Window Functions in BigQuery
• Computing a cumulative sum in BigQuery