CASE statement to do nothing

Question

I have a trigger with a case statement. In the last section of the code in the ELSE block, i want the trigger to not do anything and exit if the values do not match in the previous CASE statements. How do i do this:

create or replace TRIGGER  "CONVEYANCE_REQUEST_T3"  
BEFORE  
insert or update on "CONVEYANCE_REQUEST"  
for each row  
begin  

CASE   
 when :NEW.REGULAR_TRAVEL_MODE = '2 WHEELER' THEN  
 BEGIN  
     CASE  
         when :NEW.WAY_TYPE = 'ONE WAY' THEN   
              SELECT RATE_PER_KM * (SELECT DISTANCE_ONEWAY  FROM     DISTANCE_MASTER WHERE PROJECT_CODE = :NEW.PROJECT_CODE ) * 1 INTO     :NEW.REGULAR_AMOUNT FROM CONVEYANCE_RATE WHERE TRAVEL_MODE = :NEW.REGULAR_TRAVEL_MODE;  
         WHEN :NEW.WAY_TYPE ='TWO WAY' THEN  
              SELECT RATE_PER_KM * (SELECT DISTANCE_ONEWAY FROM DISTANCE_MASTER WHERE PROJECT_CODE = :NEW.PROJECT_CODE ) * 2 INTO :NEW.REGULAR_AMOUNT FROM CONVEYANCE_RATE WHERE TRAVEL_MODE = :NEW.REGULAR_TRAVEL_MODE;  
     END CASE;  
 END;  
 when :NEW.REGULAR_TRAVEL_MODE = '4 WHEELER' THEN  
 BEGIN  
     CASE  
        when :NEW.WAY_TYPE = 'ONE WAY' THEN   
              SELECT RATE_PER_KM * (SELECT DISTANCE_ONEWAY   FROM DISTANCE_MASTER WHERE PROJECT_CODE = :NEW.PROJECT_CODE ) * 1 INTO :NEW.REGULAR_AMOUNT FROM CONVEYANCE_RATE WHERE TRAVEL_MODE = :NEW.REGULAR_TRAVEL_MODE;  
         WHEN :NEW.WAY_TYPE ='TWO WAY' THEN  
              SELECT RATE_PER_KM * (SELECT DISTANCE_ONEWAY  FROM DISTANCE_MASTER WHERE PROJECT_CODE = :NEW.PROJECT_CODE ) * 2 INTO :NEW.REGULAR_AMOUNT FROM CONVEYANCE_RATE WHERE TRAVEL_MODE = :NEW.REGULAR_TRAVEL_MODE;  
     END CASE;  
 END;
 ELSE 
  ****statement to just exit & not do anything*****

 END CASE;  
END;  

Solution

That's an awfully complicated case statement, repeating essentially the same sort of select statement each time.

Since the only difference between each statement is whether the multiplier is 1 or 2, you could just rewrite the whole thing in one case statement containing a sql statement with a case expression like so:

create or replace trigger  "CONVEYANCE_REQUEST_T3"  
before  
insert or update on "CONVEYANCE_REQUEST"  
for each row  
begin  
  case when rate_per_km in ('2 WHEELER', '4 WHEELER')
            and new.way_type in ('ONE WAY', 'TWO WAY') then
            select rate_per_km
                     * (select distance_oneway from distance_master where project_code = :new.project_code)
                     * case when :new.way_type = 'ONE WAY'
                                 then 1
                            when :new.way_type = 'TWO WAY'
                                 then 2
                            else null
                     end regular_amount
            into   :new.regular_amount
            from   conveyance_rate where travel_mode = :new.regular_travel_mode;
       else null;
  end case; 
end;
/

You could even get away with not having the outer case statement (although you'd have to push the rate_per_km in ('2 WHEELER', '4 WHEELER') condition into the case expression, but then the query would have to be run every time for each row and that might reduce performance if most of your rows being inserted aren't 2 or 4 wheelers.

Comments

[Chris]

if you don't want it to do anything then don't put an else in there?

[Teju]

i tired it..but when i want to insert i record using a APEX application it gives the following error: 1 error has occurred ORA-06592: CASE not found while executing CASE statement ORA-06512: at "BABBLER_GROUP.CONVEYANCE_REQUEST_T3", line 3 ORA-04088: error during execution of trigger 'BABBLER_GROUP.CONVEYANCE_REQUEST_T3'

[Florin Ghita]

maybe else null;?

[Teju]

thanks florin..thats exactly what I wanted.