Home » Developer & Programmer » Reports & Discoverer » IS it possible formula column in lexical paramter
IS it possible formula column in lexical paramter [message #543900] Fri, 17 February 2012 08:30 Go to next message
helloworld_28
Messages: 60
Registered: October 2010
Member
hi to all,

i want to run

query like ...

select * from &c_tab

where c_tab is formula column

in that

function
if :p_no = 1 then
return 'dept'
else
return
'emp'

end if;
end ;

it show error

error like ora 00093 invalid table...


please help me.....
Re: IS it possible formula column in lexical paramter [message #543904 is a reply to message #543900] Fri, 17 February 2012 09:03 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't do it that way. The query is worked out before any formula columns.
You would need to pass the table name in as a parameter. And even then you would need to set a default value on that parameter so that the report can validate the query. Report Builder Help tells you these things if you bother to read it:

Report Builder help topic: About lexical references
Before you create your query, define a column or parameter in the data model for each lexical reference in the query. For columns, you must set the Value if Null property, and, for parameters, you must set the Initial Value property. Reports Builder uses these values to validate a query with a lexical reference

Your example would never work though, those tables have different columns. You would have to have two seperate reports.
Re: IS it possible formula column in lexical paramter [message #543907 is a reply to message #543904] Fri, 17 February 2012 10:29 Go to previous messageGo to next message
helloworld_28
Messages: 60
Registered: October 2010
Member
thanks sir,

i have open reports in oracle apps

where

select .............
mtl_txn_source_types mtst,
mtl_item_locations loc
&C_source_from
&C_from_cat


formula column......


function C_source_fromFormula return VARCHAR2 is
begin

if :P_source_type_id = 2 then return(',MTL_SALES_ORDERS mkts');
else if :P_source_type_id = 3 then return(',GL_CODE_COMBINATIONS gl');
else if :P_source_type_id = 6 then return(',MTL_GENERIC_DISPOSITIONS mdsp');
else if :P_source_type_id = 5 then
if :C_source_where is null then
return NULL;
else
return(',WIP_ENTITIES wip');
end if;
else if :P_source_type_id = 8 then return(',MTL_SALES_ORDERS mkts');
else if :P_source_type_id is null then
return(',MTL_SALES_ORDERS mkts,
MTL_GENERIC_DISPOSITIONS mdsp,
GL_CODE_COMBINATIONS gl ');
else null;
end if; end if; end if; end if; end if; end if;
RETURN NULL; end;


ok

why even
simple lexical referenced query is not working...

select * from &tab

which show me

invaild column name
Re: IS it possible formula column in lexical paramter [message #543908 is a reply to message #543907] Fri, 17 February 2012 10:39 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you read any part of my post?
I told you you can't use a formula column here.
I quoted the help topic that explains the error.
What do you not understand?
Re: IS it possible formula column in lexical paramter [message #543909 is a reply to message #543908] Fri, 17 February 2012 10:41 Go to previous messageGo to next message
helloworld_28
Messages: 60
Registered: October 2010
Member
sir,

i want to know what would be then &c_source_from ...

....
Re: IS it possible formula column in lexical paramter [message #543910 is a reply to message #543904] Fri, 17 February 2012 10:47 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Fri, 17 February 2012 15:03

You would need to pass the table name in as a parameter.

Re: IS it possible formula column in lexical paramter [message #543912 is a reply to message #543910] Fri, 17 February 2012 10:58 Go to previous messageGo to next message
helloworld_28
Messages: 60
Registered: October 2010
Member
sir,

formula column does not play any role here ....i confused with lexical parameters and formula column have same c_source_from name ...

iam bit confused

.....

Re: IS it possible formula column in lexical paramter [message #543914 is a reply to message #543912] Fri, 17 February 2012 11:03 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
That makes two of us.
I suggest you read the report builder help topic "About lexical references"
Re: IS it possible formula column in lexical paramter [message #543915 is a reply to message #543914] Fri, 17 February 2012 11:07 Go to previous messageGo to next message
helloworld_28
Messages: 60
Registered: October 2010
Member

sorry for disturbances

last

i have attached that .rdf

look at the formula column

q1 query....


thanks and sorry ....
  • Attachment: INVDRRSV.rdf
    (Size: 356.00KB, Downloaded 58 times)
Re: IS it possible formula column in lexical paramter [message #543918 is a reply to message #543915] Fri, 17 February 2012 11:16 Go to previous message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
I see a lot of very badly formatted code.
I see you don't know how to use ELSIF.
I don't see a q1 query.

I don't know what you expect me to tell you apart from what I already said.
Previous Topic: Unable to Generate to File 'XML' in Character Mode
Next Topic: dates as columns
Goto Forum:
  


Current Time: Tue Sep 23 02:11:06 CDT 2014

Total time taken to generate the page: 0.24769 seconds