4 Replies Latest reply on Mar 28, 2018 8:14 PM by TexasApexDeveloper

    LOV fails with IF, ELSEIF, ELSE structure

    3580908

      11Gex / Apex 5.02

       

      I've used LOV's before that return a select list one way or another, based on a page item value and an IF, ELSE structure... like this:

      IF :P123_CHOICE = 'EMP' THEN
        RETURN
        'SELECT ename d, empno r
         FROM emp';
      ELSE
        RETURN
        'SELECT dname d, deptno r
         FROM dept';
      END IF;
      

       

      However, when I attempt to do this with an IF, ELSEIF, ELSE structure this - if fails:

      if :P0_alevel in (1,4) then
         return
           'select Name as d,
             ID as r
            from REGION
            order by 1';
      elseif :P0_COA is not null then
         return
           'select Name as d,
             ID as r
            from REGION
            where ID not in (1,6)
            order by 1';
      else
         return
           'select Name as d,
             ID as r
            from REGION
            where ID = :P0_OFFICEID
            order by 1';
      end if;
      

       

      Here is the debug detail:

      ...Execute Statement: declare function x return varchar2 is begin if :P0_alevel in (1,4) then     return       'select Name as d,         ID as r        from REGION        order by 1';  elseif :P0_COA is not null then     return       'select Name as d,         ID as r        from REGION        where ID not in (1,6)        order by 1';  else     return       'select Name as d,         ID as r        from REGION        where ID = :P0_OFFICEID        order by 1';  end if;  return null; end; begin wwv_flow.g_computation_result_vc := x; end;

       

      Add error onto error stack

       

      Error data:

       

      message: Unable to determine List of Values.

       

      additional_info: ORA-06550: line 1, column 172:

       

      PLS-00103: Encountered the symbol "" when expecting one of the following:

         := . ( @ % ;
      The symbol ":=" was substituted for "" to continue.
      ORA-06550: line 1, column 192:
      PLS-00103: Encountered the symbol "THEN" when expecting one of the following:

         ; and or
      The symbol "; was inserted before "THEN" to continue.

       

      Have I missed something in the syntax, or am I asking the LOV to do more than it is capable of?

      I appreciate your responses and the time you take to help me out.

       

      Rich

        • 1. Re: LOV fails with IF, ELSEIF, ELSE structure
          fac586

          3580908 wrote:

          Update your forum profile with a recognisable username instead of "3580908": Video tutorial how to change nickname available

           

          Help us to help you. Always follow these guidelines when posting a question in this forum: how to get answers from forum

          11Gex / Apex 5.02

           

          I've used LOV's before that return a select list one way or another, based on a page item value and an IF, ELSE structure... like this:

          1. IF:P123_CHOICE='EMP'THEN
          2. RETURN
          3. 'SELECTenamed,empnor
          4. FROMemp';
          5. ELSE
          6. RETURN
          7. 'SELECTdnamed,deptnor
          8. FROMdept';
          9. ENDIF;

           

          However, when I attempt to do this with an IF, ELSEIF, ELSE structure this - if fails:

          1. if:P0_alevelin(1,4)then
          2. return
          3. 'selectNameasd,
          4. IDasr
          5. fromREGION
          6. orderby1';
          7. elseif:P0_COAisnotnullthen
          8. return
          9. 'selectNameasd,
          10. IDasr
          11. fromREGION
          12. whereIDnotin(1,6)
          13. orderby1';
          14. else
          15. return
          16. 'selectNameasd,
          17. IDasr
          18. fromREGION
          19. whereID=:P0_OFFICEID
          20. orderby1';
          21. endif;

           

          Here is the debug detail:

          ...Execute Statement: declare function x return varchar2 is begin if :P0_alevel in (1,4) then return 'select Name as d, ID as r from REGION order by 1'; elseif :P0_COA is not null then return 'select Name as d, ID as r from REGION where ID not in (1,6) order by 1'; else return 'select Name as d, ID as r from REGION where ID = :P0_OFFICEID order by 1'; end if; return null; end; begin wwv_flow.g_computation_result_vc := x; end;

           

          Add error onto error stack

           

          Error data:

           

          message: Unable to determine List of Values.

           

          additional_info: ORA-06550: line 1, column 172:

           

          PLS-00103: Encountered the symbol "" when expecting one of the following:

          := . ( @ % ;
          The symbol ":=" was substituted for "" to continue.
          ORA-06550: line 1, column 192:
          PLS-00103: Encountered the symbol "THEN" when expecting one of the following:

          ; and or
          The symbol "; was inserted before "THEN" to continue.

           

          Have I missed something in the syntax, or am I asking the LOV to do more than it is capable of?

          I appreciate your responses and the time you take to help me out.

           

          The keyword is ELSIF not ELSEIF.

          • 2. Re: LOV fails with IF, ELSEIF, ELSE structure
            Alli Pierre Yotti

            There is no "ELSEIF" in PL/SQL. have a look here

             

            https://www.techonthenet.com/oracle/loops/if_then.php

            1 person found this helpful
            • 3. Re: LOV fails with IF, ELSEIF, ELSE structure
              3580908

              Thanks fac586, Alli Pierre Yotti ...

               

              I stared at this for so long I got crosseyed.  I actually used the site Alli refers to before posting my question, and still did not see the spelling error.

               

              Thanks guys.

               

              Rich

              • 4. Re: LOV fails with IF, ELSEIF, ELSE structure
                TexasApexDeveloper

                I for one would also look at using a case statement here versus the if/then/else/elsif..

                 

                Thank you,

                 

                Tony Miller

                Los Alamos, NM