4 Replies Latest reply: Apr 7, 2014 8:53 AM by Doolius RSS

    if then statement not liking "and" help

    Doolius

      The below trigger compiles and works fine when the "AND :new.max_event_date := :new.CHY_ATD_DATE" is removed. When it is there I get the errors:

       

      Compilation failed, line 15 (11:06:29) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects

      the compilation of database triggers. PLS-00103: Encountered the symbol "="  when expecting one of the following: . ( * @ % & = - + ; < / > at in

      is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || indicator multiset member
      submultiset The symbol "* was inserted before "=" to continue.

      Compilation failed, line 17 (11:06:29) The line numbers associated with compilation errors
      are relative to the first BEGIN statement. This only affects the compilation of database triggers. PLS-00103: Encountered the symbol "=" when expecting one
      of the following: . ( * @ % & = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || indicator multiset member submultiset The symbol "* was inserted before "=" to continue.

      create or replace TRIGGER "SGD_OEF_ITV_NDN_TRU" 
      BEFORE  
        UPDATE 
      ON OEF_ITV_NDN FOR 
      EACH ROW  
      BEGIN  
        :NEW.UPDATED_DATE := 
      SYSDATE;  
        :NEW.UPDATED_BY   := 
      NVL(V('P1_USER_ID'),USER);
        BEGIN
          IF ( :new.POD = 'JN4' 
      )
          THEN
          if (NVL(:OLD.CHY_ATD_DATE, 
      date '1800-01-01') 
      != NVL(:NEW.CHY_ATD_DATE, date '1800-01-01')) 
          THEN  :new.max_event :='ATD 
      CHY';
          elsif  (NVL(:OLD.CHY_ETD_DATE, 
      date '1800-01-01') 
      != NVL(:NEW.CHY_ETD_DATE, date '1800-01-01'))  
          THEN  :new.max_event := 
      'ETD CHY';
                 end 
      if;
          elsif 
      ( :new.POD in ('LQ1','LQ2'))
          THEN
         if  (NVL(:OLD.CHY_ATD_DATE, 
      date '1800-01-01') 
      != NVL(:NEW.CHY_ATD_DATE, date '1800-01-01'))  
          THEN :NEW.MAX_EVENT :='ATD 
      CHY' 
          AND 
      :new.max_event_date := :new.CHY_ATD_DATE;
          elsif  (NVL(:OLD.CHY_ETD_DATE, 
      date '1800-01-01') 
      != NVL(:NEW.CHY_ETD_DATE, date '1800-01-01'))   
          THEN :NEW.MAX_EVENT :='ETD 
      CHY' 
          AND 
      :new.max_event_date := :new.CHY_ETD_DATE;
          END 
      if;
          ELSE
            
        :NEW.MAX_EVENT := 
      'ERRORED';
          END IF;
          END;
      END;
      
      
      

       

      How do I need to code this so the THEN statement works with the AND.

       

      Thanks,

      Steven

        • 1. Re: if then statement not liking "and" help
          Joe R

          Steven,

           

          Take out the colon-equal and put just equal. Colon-equal is assignment and you're doing a comparison.

           

          Thanks,
          Joe

          • 2. Re: if then statement not liking "and" help
            Doolius

            Joe,

            If I'm understanding that correctly:

             

            The THEN statement is assigning the NEW.MAX_EVENT equal to "ATD CHY" and the NEW.MAX_EVENT_DATE equal to the NEW.CHY_ATD_DATE.

            The only comparison that should be going on is after the IF/ELSEIF and before the THEN

             

                IF ( :new.POD = 'JN4' )
              
              THEN
                       if (NVL(:OLD.CHY_ATD_DATE, date '1800-01-01') != NVL(:NEW.CHY_ATD_DATE, date '1800-01-01')) 
                  THEN  :new.max_event :='ATD CHY';
                         elsif  (NVL(:OLD.CHY_ETD_DATE, date '1800-01-01') != NVL(:NEW.CHY_ETD_DATE, date '1800-01-01'))  
                             THEN  :new.max_event := 'ETD CHY';
                       end 
            if;
            

            That IF statement compiles fine. It only doesn't compile when I try and set a value to NEW.MAX_EVENT_DATE.

             

            But when I do take out the colon and just set it equal like below:

            THEN :NEW.MAX_EVENT :='ATD CHY' AND :new.max_event_date = :new.chy_atd_date;
            

             

            I get more errors.

             

            Steven

            • 3. Re: if then statement not liking "and" help
              Joe R

              Steven,

               

              The "AND" is a comparison and not an assignment.

              Try:

              THEN

                  :NEW.MAX_EVENT := 'ATD CHY';

                  :NEW.MAX_EVENT_DATE := :NEW.CHY_ATD_DATE;

              END IF;

               

              Thanks,

              Joe

              • 4. Re: if then statement not liking "and" help
                Doolius

                Ahh Duh. Thanks Joe!