sqloracle-databasedecodeora-00936

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


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

Solution

  • 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