Adding date field with case statement leads to missing expression fault message, how to solve?-CodePudding

update r5events e       
set         
 e.evt_udfdate10 =  e.evt_reported      
  CASE  
    when to char(e.evt_reported, 'DD/MM/YYYY') = ('07/01/2023' ) then 5
  end,

tried also to_date instead of to_char

CodePudding user response：

Aside from the missing _ in to_char, the trailing comma at the end will throw an error if that's your full SQL statement.

CodePudding user response：

Actually, you should compare date to date, not convert date to string and then compare string to string.

Sample data:

SQL> select * From r5events;

        ID EVT_REPORT EVT_UDFDAT
---------- ---------- ----------
         1 07/01/2023             --> add 5 to this date because it is equal to 7th of January 2023
         2 25/12/2022             --> you didn't say what to do if date isn't 7th of January 2023,
                                      so I'm leaving it "as is"

Update statement:

SQL> update r5events e set
  2    e.evt_udfdate10 =
  3      e.evt_reported   case when e.evt_reported = date '2023-01-07' then 5
  4                            else 0
  5                       end;

2 rows updated.

Result:

SQL> select * From r5events;

        ID EVT_REPORT EVT_UDFDAT
---------- ---------- ----------
         1 07/01/2023 12/01/2023  --> 5 days added to EVT_REPORTED
         2 25/12/2022 25/12/2022  --> nothing has been added

SQL>

update might need to be adjusted if evt_reported contains time component different from midnight. It would be easier to assist if you posted table description, sample data and desired result.