Home » Developer & Programmer » Reports & Discoverer » Adding new parameter for Ageing 7 Bucket report
Adding new parameter for Ageing 7 Bucket report [message #421523] Wed, 09 September 2009 01:13 Go to next message
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 #421531 is a reply to message #421523] Wed, 09 September 2009 02:03 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"Invalid identifier" means that query, in which you are using this lexical parameter, doesn't contain a table whose name (or alias) is "fv" and/or this table doesn't contain a column whose name is "flex_value".

How does the query look like?
Re: Adding new parameter for Ageing 7 Bucket report [message #421535 is a reply to message #421531] Wed, 09 September 2009 02:15 Go to previous messageGo to next message
ndr301
Messages: 10
Registered: July 2009
Junior Member
hi..
thanks littlefoot.
Here i attach the select statement.
thanks
Re: Adding new parameter for Ageing 7 Bucket report [message #421539 is a reply to message #421535] Wed, 09 September 2009 02:21 Go to previous messageGo to next message
ndr301
Messages: 10
Registered: July 2009
Junior Member
hi..
sorry.. Here the attachment
Re: Adding new parameter for Ageing 7 Bucket report [message #421554 is a reply to message #421523] Wed, 09 September 2009 04:08 Go to previous messageGo to next message
cookiemonster
Messages: 12409
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.
Re: Adding new parameter for Ageing 7 Bucket report [message #421557 is a reply to message #421539] Wed, 09 September 2009 04:15 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I can't tell for sure, but I guess that this belongs to Oracle Apps., hence that awful piece of code.

However, attaching a file with ~1500 lines of code and expecting someone to debug it for you is ridiculous. One of the last lines
srw.message('1003','l_cus_sel = ' || l_cus_sel);
is supposed to display the final statement. As Cookiemonster has said, review the result and check why FV.FLEX_VALUE is said to be an "invalid identifier".
Re: Adding new parameter for Ageing 7 Bucket report [message #421692 is a reply to message #421557] Wed, 09 September 2009 20:38 Go to previous message
ndr301
Messages: 10
Registered: July 2009
Junior Member
thanks guys..
i'll try to configure the sql first.
thanks a lots.
Previous Topic: REP-1814: Report cannot be formatted. Object 'vertically' can never fit within
Next Topic: Bursting and Distributing a Report Error
Goto Forum:
  


Current Time: Tue Dec 06 12:31:53 CST 2016

Total time taken to generate the page: 0.10657 seconds