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