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.