| IS it possible formula column in lexical paramter [message #543900] |
Fri, 17 February 2012 08:30  |
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  |
cookiemonster
Messages: 13959
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 referencesBefore 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 |
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
