Decode statement in SQL results in ORA-00936: missing expression

Question

Trying to figure out why this decode isn't working. I just want to create a column that would separate the above line into <5 and >=5 and give them a corresponding answer. I really can't seem to see why it is not working, I keep getting ORA-00936: missing expression.

SELECT a.account_id "Account ID",
   a.first_name ||' '|| a.last_name "Name",
   b.date_and_time_created "Date Created",
   c.date_and_time_ordered "Date Ordered",
   c.date_and_time_ordered - b.date_and_time_created "Days Ordered After Created",
   DECODE ((c.date_and_time_ordered - b.date_and_time_created), <5, 'No Reminder Needed',
                                                              >=5, 'Reminder Needed', ' ') "Reminder"
FROM shopper a 
   JOIN shopping_cart b ON a.account_id = b.shopper_account_id
   JOIN orders c ON a.account_id = c.shopper_account_id

Answer 1

the problem causes due to usage of < > = within a decode statement , use this instead :

SELECT a.account_id "Account ID",
   a.first_name ||' '|| a.last_name "Name",
   b.date_and_time_created "Date Created",
   c.date_and_time_ordered "Date Ordered",
   c.date_and_time_ordered - b.date_and_time_created "Days Ordered After Created",
   (case when ( c.date_and_time_ordered - b.date_and_time_created  <  5 ) then 'No Reminder Needed'
         when ( c.date_and_time_ordered - b.date_and_time_created  >= 5 ) then 'Reminder Needed'
         else ' '
     end ) "Reminder"
FROM shopper a 
   JOIN shopping_cart b ON a.account_id = b.shopper_account_id
   JOIN orders c ON a.account_id = c.shopper_account_id

Answer 2

Or, if you insist on DECODE (though, I don't know why would you want to do that as CASE is much more appropriate here), you could use SIGN function, such as

SELECT DECODE (
          SIGN ( (c.date_and_time_ordered - b.date_and_time_created) - 5),
          -1, 'less than 5',
          'greater than or equal to 5')
          result
  FROM ...