CODEWITHSUNDEEP
sqldatabaseoracle-databasecaseprocedure
Doolius
Doolius

Reputation: 874

Update procedure with Case statement

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;

Upvotes: 0

Views: 675

Answers (1)

Roberto Navarro
Roberto Navarro

Reputation: 958

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;

Upvotes: 1

Related Questions