Urgent, please help! - Forms

Hello,
I am constructing a block using data source = FROM SELECT CLAUSE, and the statement is as follows:
SELECT EMP_NAME FROM EMPLOYEES WHERE EMP_NO = :CONTROL.EMP_NO
But it causes invalid column name. Is there a method to reference the value indirectly? Thanks.
Burt 

use the set_block_property to set the From_clause dynamically at runtime.

Related

Lexical Parameter

Hai,
I have a doubt in using the Lexical Parameter in the select clause. As given in the documentation when I tried to create the SQL query
select &p_empno eno,&p_job role from emp after creating the required parameter in the user parameter I getting the following error as
eno, role invalid identifier. But when I tried to give any of the database column as alias name the query is getting accepted...
It may be familiar topic for all u guys, but please help me out in this case as of how to declare the lexical Parameter in the select clause without using the database name as alias name. I have gone through the earlier forums and the documentation but not able to get it correct...Help me out in this case
Thanks in Advance. 
Hi,
set a default value for the parameters p_empno and p_job. The query will be validated, when applied in the dialog. If p_empno is empty, so the query
select &p_empno eno from emp
will result in
select eno from emp
which is invalid.
If p_empno has a valid default value like empno, the result is
select empno eno from emp
which is valid and the column has your wished alias name.
Regards
Rainer 
It worked I have not assigned the Inital Value previously. Hence was facing the problem. Now I got it right.
thanks.

parameter in the report

Hi Experts,
I want that i shall pass one column name of a table (EMP) and in the report i shall print the column of the database for example if i pass the
string 'Ename' via a parameter to report the report should print all the Enames of the EMp table.
Suppose the user parameter which i have defined in the Report side is param_name and in the SQL query of the report i am writting that
Select :param_name from emp;
but the result it is giving me is.
Param_name1
ename
ename
ename
ename
Can any one suggest.
Regards
Rajat 
This is a Reports question and it should be asked in the Reports Forum. but here's a little something you can start with.
You need to use lexical parameter in your select statement of the report.
SELECT &col_name FROM EMPthe lexical parameter will be substituted with the column name in your SQL. For more information search for lexical parameters in the report's builder online-help.
Tony 
Hi tony,
This is very urgent requirement so can you please tell me the way to get the data i have tried with &col_1 but no luck.
will you please help me.
Regards
Rajat 
Write dynamic query to report
in query
just put &query, and initialise to '1'
after passing parameter from form to report you construnct select query dynamically.
'select '||:param||'from emp';
Regards
venkata. 
hi venkata,
will you please tell me how can i do it.
n query
just put &query, and initialise to '1'
I am unable to understand it.In query where can i put &query.
please help.
regards
rajat 
i would go with Tonys suggestion:
Create a user-parameter with name COL_NAME, datatype char and set the initial value to en existing columnname in your table (like ENAME for emp-table)
Then set your query-text to
SELECT COL
  FROM (SELECT &COL_NAME COL
          FROM EMP
       )This works for me.

How to exclude list of employess in the parameter?

Hi I have one report that shows no on employeess,
I want to do a parameter that help users to exclude some emp_no for the list , i wrote the parameter as
emp_no no in :empno
However i can wirte only one emp no in this case
how can i let user to exclude group on emp no at on times?
i wish that i explain my problem well
Thank you 
Hello,
You can use a lexical reference :
emp_no not in (&empno)
When you enter the empno values, use the comma to separate the values : example : 123,456,789,147,258,369
Regards 
Other way is to store emp numbers in temprorary table. Then in your select add the where clause wich not in stored emp. 
Hi,
You can use :from_empno & :to_empno with the sql operator 'between <from> and <to>'
You can even achive using the lexical parameters. check out this link for more information
http://oracleapps4u.blogspot.com/2011/03/creating-parameters-for-report.html
Regards,
KK

BLOCK BASED ON SELECT - HELP

Hi,
I have a block named "BLK_01". This block contains 2 text items: "ITEM_01" and "ITEM_02".
I want to populate this block by using the following query:
SELECT EMPNO, ENAME FROM EMP
The column "EMPNO" will be returned in the item "ITEM_01" and the column "ENAME" will be returned in the item "ITEM_02".
How can i do that? Can anyone tell me step-by-step? What settings do I have to set?
Thx!
PS.: i will not use view, the block has to be based on that query (Query Data Source Type = From Cause Query) 
hy,
if you have access to metalink ,see
Note:69884.1
How to use From Clause Query in Forms 
hey, thanks, but i already have done... it worked.

ORDER BY CLAUSE IN LIST

HAI
Is it possible to include the order by clause while populating a list box using create_group function.
eg:
select name from table1 order by name 
CREATE_GROUP_FROM_QUERY takes any valid sql statements.
only thing you have to take care if you are populating the list box based on record group is to select 2 columns inthe select statement, One for label of the listbox element and another for value.
select name, name from table1 order by name 
hai
Actually I have given the sql query as u said. But it dosn't populate.
RGID:=CREATE_GROUP_FROM_QUERY('RG1','SELECT name,name FROM table1 ORDER BY NAME');
X:=POPULATE_GROUP('RG1');
POPULATE_LIST('BLOCK1.LIST1','RG1');
But this command will not populate the list. If ORDER BY clause is removed, then it will work.
That is my poblem. 
Your RG does not populate because your SQL is not correct. You order by name, but you have two columns named 'name' so it is ambigious for the compiler.
Try something like:
SELECT name as name1, name FROM table1 ORDER BY NAME
teo 
Hai,
Thank u very much.
Now My problem is solved.
bye.

Categories

Resources