| Adding new parameter for Ageing 7 Bucket report [message #421523] |
Wed, 09 September 2009 01:13  |
ndr301
Messages: 10
Registered: July 2009
|
Junior Member |
|
|
hi all,
I in the middle of adding new parameter for Building Name Low and Building Name High (building name range). But it seem that I cannot get where actually can i put this statement :
if :p_building_low is not null then
:lp_building_low := ' and fv.flex_value >= :p_building_low ';
end if;
if :p_building_high is not null then
:lp_building_high := ' and fv.flex_value <= :p_building_high ';
end if;
I already put it inside AfterPForm function but it not work well n give me this error when i run the report:
ORA-00904: "FV"."FLEX_VALUE": invalid identifier
Why did this happend because i already select the field??
Do anyone can guide me on this. Thanks in advance.
|
|
|
|
|
|
|
|
|
|
| Re: Adding new parameter for Ageing 7 Bucket report [message #421554 is a reply to message #421523] |
Wed, 09 September 2009 04:08  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That is the most complicated piece of dynamic sql I've ever seen. I would suggest that it's so complicated it's basically unmaintainable. In fact if you're having to write that much code to generate a select I would suggest there's something seriously wrong with your data model.
The only thing you can do here, other than completely rewriting it to something more understandable, is to use standard debugging techniques for dynamic sql.
Output the string with the final select statement and then run it sqlplus - then the problem should become obvious.
But I would seriously look at rewriting this from scratch. Using materialized views might help a lot.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
