How to display the number of days elapsed between two dates denoted in columns-specifically those exceeding 30 days?

Question

Perhaps I'm overthinking this Oracle SQL query, but I'm attempting to display the number of days elapsed between two column data-sets attributed as "order-date" and "ship-date", which will be displayed within a new alias column, "Number of Days elapsed"- so far, I've constructed the following query:

SELECT order_id,
       (shipped_date - order_date) AS "Number of Days elapsed"
FROM orders;

Evidently, an alias column can't be sorted or filtered- so what options are available to filter the resulting output for the number of orders exceeding 30 days to ship?

Answer 1

Either restate the calculation in the predicate

SELECT order_id,
       (shipped_date - order_date) AS "Number of Days elapsed"
FROM orders
WHERE shipped_date - order_date > 30
ORDER BY (shipped_date - order_date) desc;

or nest the query

SELECT order_id, "Number of Days elapsed"
  FROM (SELECT order_id,
               (shipped_date - order_date) AS "Number of Days elapsed"
          FROM orders)
 WHERE "Number of Days elapsed" > 30
 ORDER BY "Number of Days elapsed" desc

If you are going to nest the query, though, it probably makes sense to pick a case-insensitive alias in the inner query and to do the case-sensitive aliasing later on

SELECT order_id, days_elapsed "Number of Days elapsed"
  FROM (SELECT order_id,
               (shipped_date - order_date) days_elapsed
          FROM orders)
 WHERE days_elapsed > 30
 ORDER BY days_elapsed  desc