0

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?

2
  • I don't have oracle available to tinker on, but have you tried something along the lines of WHERE (shipped_date - order_date) > 30 ORDER BY (shipped_date - order_date) ? – Dan Feb 16 '16 at 21:33
  • ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause: *Action: – alexanderjsingleton Feb 16 '16 at 21:36
2

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
4
  • thank you- your first submission is the most elegant but I also appreciate the nested approaches-well done, sir! Thanks again. – alexanderjsingleton Feb 16 '16 at 22:55
  • 1
    @alexanderjsingleton - It depends. For simple calculations like this, I find repeating them in the where clause easiest. But as you get more and more complicated calculations, it makes more sense to use a nesting approach. – Justin Cave Feb 16 '16 at 22:58
  • 1
    @alexanderjsingleton - updated. – Justin Cave Feb 16 '16 at 23:05
  • LOL-just deleted my comment; I resolved via your last nested-query solution-thanks again! – alexanderjsingleton Feb 16 '16 at 23:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.