|
|
|
|
|
|
| Re: Lexical Reference [message #443359 is a reply to message #443340] |
Mon, 15 February 2010 05:02  |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I think you are right; at least, I tried it now and guess what - it doesn't work.
However, there *might* be a workaround - not very elegant, though, but - it works.
For example: I have created a report based on Scott's schema whose query looks like select * from emp &lex_where function AfterPForm return boolean is
begin
if :p_deptno is null then
:lex_where := 'where 1 = 1';
else
:lex_where := 'where deptno = ' || :p_deptno;
end if;
return (TRUE);
end;
A formula column (you are asking about; in my example, it counts number of fetched records) can't use "&lex_where"; therefore, I have created additional placeholder column called "CP_PLACE" (which will contain number of records), as well as formula column (which will calculate that number).
Formula column code: I have to simulate lexical parameter and put it into "p_where" variable; then I do the calculation using SRW built-in package, and put the result into the placeholder column. Finally, placeholder column is displayed in the paper layout.function CF_cnt_emp_in_deptFormula return Number is
p_where varchar2(200);
retval number;
begin
p_where := 'where deptno = ' || :p_deptno;
srw.do_sql('select count(*) into :cp_place from emp ' || p_where);
return (0);
end;
It appears that it works just fine; I don't know whether it helps or not, but I don't know any other way.
Note that I have done that in Reports Developer 10g (as I don't have 6i). The report is attached to this message - if possible, open it and see how it looks like.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
