Extract with Coalesce

In the following example, we are extracting data from a Sales Forecast.

We are going to compare On Order with Expected.

Forecast

Filter out SKUs

The goal is to find Forecasts where:

  • The order type is Sales
  • There is an actual_quantity_uoms less than -1
  • There is an expected_sold_quantity less than -1

⚠️ Less than 0 means the N units of SKUs have left the stock.

1
2
3
4
5

(
  coalesce(extract_forecasting.actual_order_type, '') = 'sales'
  and coalesce(extract_forecasting.actual_quantity_uoms, 0) < 0
)
or coalesce(extract_forecasting.expected_sold_quantity_uoms, 0) < 0

1. coalesce(…)

The coalesce function returns x if it is not NULL; otherwise, it returns y.

🔗 PostgreSQL documentation here

So the following ensures that actual_order_type is set to sales if it is not NULL:

1

coalesce(extract_forecasting.actual_order_type, '') = 'sales'

This gives you the order type, or an empty string if actual_order_type is NULL.

coalesce(..., 0) gives you a number, or 0 if it’s NULL.

2. First condition (inside the parentheses) 

1
2

coalesce(extract_forecasting.actual_order_type, '') = 'sales'
and coalesce(extract_forecasting.actual_quantity_uoms, 0) < 0

This checks both:

  • The order type is ‘sales’ (or an empty string if NULL, which will never equal ‘sales’).
  • The actual quantity is less than zero (negative).

So → “Is it a sales order AND does it have a negative actual quantity?”

3. Second condition (after the OR) 

1

coalesce(extract_forecasting.expected_sold_quantity_uoms, 0) < 0

This checks if the expected sold quantity is negative.

4. Combined with OR

The whole expression is TRUE if either:

  • It’s a sales order with a negative actual quantity, or
  • The expected sold quantity is negative.

Otherwise, it’s FALSE.