Update procedure with Case statement

Question

I'm needing to make an update procedure that has case statements or IF statements:

create or replace procedure "PROC_TEMP_UPDATE"
is
begin
update temp_update
set error_desc = 
CASE WHEN (indicator = NULL AND date1 != NULL)
     THEN error_desc = 'Enter an Indicator before Date1.' end,
CASE WHEN (NVL(date1, date '1800-01-01') > NVL(date2, date '1800-01-01'))
     THEN 'Date1 cannot be greater than Date2.' 
END
end proc_temp_update;

Can anyone assist me with this?

Thanks Steven

EDIT: Other code I've tried like possible duplicate example:

create or replace procedure "PROC_TEMP_UPDATE"
is begin
update temp_update
set error_desc = 

CASE WHEN (route_ind = NULL AND date1 != NULL)
    THEN error_desc = 'Enter a Route Indicator before a Date1.',
WHEN (NVL(date1, date '1800-01-01') > NVL(date2, date '1800-01-01'))
    THEN 'Date1 cannot be greater than Date2.' 
else 'Error'
END
end proc_temp_update;

possible duplicate of [UPDATE with CASE and IN - Oracle](http://stackoverflow.com/questions/5170864/update-with-case-and-in-oracle) — Maurice Reeves, Jun 17 '14 at 18:42
Other than the `IS` instead of `AS`, what problem(s) are you having? — Andrew, Jun 17 '14 at 18:42
I think this is a duplicate of http://stackoverflow.com/questions/5170864/update-with-case-and-in-oracle — Maurice Reeves, Jun 17 '14 at 18:43
I've tried something like the the URL that was posted as a possible duplicate. I get errors such as: missing keyword line 4, encountered symbol end-of-file line 13 when expecting (begin, end, case, .....) Suggestion of another way to do this? I'm using Oracle APEX and I just need code that will go through each record and validate the data. I have a lot of date columns that need to be checked to make sure they are in the correct order. — Doolius, Jun 17 '14 at 18:51
Um, why are you maintaining this in your db? This looks like it should be _application_ code, there's no reason to do this kind of thing in the db. Especially for dealing with temporal-dependent stuff. Although even that doesn't make much sense - what are you doing that you have to indicate some sort of route-indicator before whatever the date represents (probably a start date...)? — Clockwork-Muse, Jun 18 '14 at 11:43
It is just part of the data validation. The user has about 50 dates they can populate and certian sets of dates need to be populated depending on the route. This procedure will be ran against a spreadsheet of data that the user uploads. Once the records pass validation they will be moved to the final table. If they do not pass they will go to a log table and the user will see a report of which records didn't pass and why. — Doolius, Jun 18 '14 at 12:42

score 1 · Accepted Answer · edited Jun 17 '14 at 19:42

I tried compiling your original code and had some issues with the way the case statement was structured. I also don't think you meant to have the word 'DATE' as part of the string in your NVL, correct? If you're trying to cast the date, then figure out the date format and use TO_DATE function to perform that..

CREATE OR REPLACE PROCEDURE "PROC_TEMP_UPDATE"
IS
BEGIN
   UPDATE temp_update
      SET err_desc =
             CASE 
                WHEN date1 IS NOT NULL AND indicator1 IS NULL
                THEN
                   'Enter an Indicator before Date1.'
                WHEN NVL (date1, '1800-01-01') > NVL (date2, '1800-01-01')
                THEN
                   'Date1 cannot be greater than Date2.'
             END;
END proc_temp_update;

Another way is to rethink your strategy, and build the string before your UPDATE clause, as such:

CREATE OR REPLACE PROCEDURE "PROC_TEMP_UPDATE" (date_from DATE, date_to DATE)
IS
   err_desc_msg   VARCHAR2 (500);
   e_update_msg   EXCEPTION;

   tmp_date       DATE;
BEGIN
   IF date_from IS NULL OR date_to IS NULL
   THEN
      err_desc_msg := 'Enter a valid date range';
      RAISE e_update_msg;
   END IF;

   IF date_from > date_to
   THEN
      err_desc_msg := 'date_from cannot be greater than date_to';
      RAISE e_update_msg;
   END IF;

   --or swap the dates
   IF date_from > date_to
   THEN
      tmp_date := date_to;
      date_to := date_from;
      date_from := tmp_date;

      err_desc_msg := 'swapped dates';
      RAISE e_update_msg;
   END IF;
EXCEPTION
   WHEN e_update_msg
   THEN
      UPDATE temp_update
         SET err_desc = err_desc_msg;
END proc_temp_update;

The first set of code took care of one error, but I am still getting an error: Compilation failed,line 7 (14:21:49) PL/SQL: ORA-00905: missing keywordCompilation failed,line 4 (14:21:49) PL/SQL: SQL Statement ignored — Doolius, Jun 17 '14 at 19:22
for the first code: I removed the "Case err_desc" and replaced it with just "case" and the code compiled fine. — Doolius, Jun 17 '14 at 19:30
Good deal, glad I could help.. I updated my example with your comment — Roberto Navarro, Jun 17 '14 at 19:42

Update procedure with Case statement

1 Answers1