Home » Developer & Programmer » Reports & Discoverer » lexicalparameters
| lexicalparameters [message #119844] |
Mon, 16 May 2005 06:19  |
sivaprasadravipati
Messages: 7
Registered: May 2005
Location: hyderabad
|
Junior Member |
|
|
hi
i am siva.in reports how do generate a reports by using lexicalparameters.please tell me replay any body as soon as possible and also send me brief description about lexicalparameters.
|
|
|
|
| Re: lexicalparameters [message #121019 is a reply to message #119844] |
Wed, 25 May 2005 07:42  |
Gurusubramanyam
Messages: 79
Registered: July 2001
|
Member |
|
|
Hi,
Please find the below text from the Reports helpfile.
Bye,
G.S
Lexical references
Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL. Look at the example below.
You create a lexical reference by entering an ampersand (&) followed immediately by the column or parameter name. A default definition is not provided for lexical references. Therefore, you must do the following:
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 enter Value if Null, and, for parameters, you must enter Initial Value. Report Builder uses these values to validate a query with a lexical reference.
Create your query containing lexical references.
Lexical Reerence Examples
SELECT Clause
SELECT &P_ENAME NAME, &P_EMPNO ENO, &P_JOB ROLE FROM EMP
P_ENAME, P_EMPNO, and P_JOB can be used to change the columns selected at runtime. For example, you could enter DEPTNO as the value for P_EMPNO on the Runtime Parameter Form. Note that in this case, you should use aliases for your columns. Otherwise, if you change the columns selected at runtime, the column names in the SELECT list will not match the Report Builder columns and the report will not run.
FROM Clause
SELECT ORDID, TOTAL FROM &ATABLE
ATABLE can be used to change the table from which columns are selected at runtime. For example, you could enter ORD for ATABLE at runtime. If you dynamically change the table name in this way, you may also want to use lexical references for the SELECT clause (look at the previous example) in case the column names differ between tables.
WHERE Clause
SELECT ORDID, TOTAL FROM ORD WHERE &CUST
CUST can be used to restrict records retrieved from ORD. Any form of the WHERE clause can be specified at run-time.
GROUP BY Clause
SELECT NVL(COMMPLAN, DFLTCOMM) CPLAN, SUM(TOTAL) TOTAL FROM ORD GROUP BY &NEWCOMM
The value of NEWCOMM can be used to define the GROUP BY clause.
HAVING Clause
SELECT CUSTID, SUM(TOTAL) TOTAL FROM ORD GROUP BY CUSTID HAVING &MINTOTAL
The value of MINTOTAL could, for example, be used to select customers with a minimum total of orders.
ORDER BY Clause
SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL FROM ORD ORDER BY &SORT
The value of SORT can be used to select SHIPDATE, ORDERDATE, ORDID, or any combination as the sort criterion. It could also be used to add on to the query, for example to add a CONNECT BY and START WITH clause.
CONNECT BY and START WITH Clauses
Parameters in CONNECT BY and START WITH clauses are used in the same way as they are in the WHERE and HAVING clauses.
Multiple Clauses
SELECT &COLSTABLE
COLSTABLE could be used to change both the SELECT and FROM clauses at runtime. For example, you could enter DNAME ENAME, LOC SAL FROM DEPT for COLSTABLE at runtime.
SELECT * FROM EMP &WHEREORD
WHEREORD could be used to change both the WHERE and ORDER BY clauses at runtime. For example, you could enter WHERE SAL > 1000 ORDER BY DEPTNO for &WHEREORD at runtime.
PL/SQL and SQL
SELECT &BREAK_COL C1, MAX(SAL) FROM EMP GROUP BY &BREAK_COL
BREAK_COL is used to change both the SELECT list and the GROUP BY clause at runtime. The Initial Value of the parameter &BREAK_COL is JOB. At runtime, the user of the report can provide a value for a parameter called GROUP_BY_COLUMN (of Datatype Character). In the Validation Trigger for GROUP_BY_COLUMN, you call the following PL/SQL procedure and pass it the value of GROUP_BY_COLUMN:
procedure conv_param (in_var IN char) is
begin
if upper(in_var) in ('DEPTNO','EMPNO','HIREDATE') then
:break_col := 'to_char('||in_var||')' ;
else
:break_col := in_var;
end if;
end;
This PL/SQL ensures that, if necessary, a TO_CHAR is placed around the break column the user chooses. Notice how in SQL, you make a lexical reference to BREAK_COL. In PL/SQL, you must make a bind reference to BREAK_COL because lexical references are not allowed in PL/SQL.
Lexical reference restrictions
You cannot make lexical references in a PL/SQL statement.
If a column or parameter is used as a lexical reference in a query, its Datatype must be Character.
If you want to use lexical references in your SELECT clause, you should create a separate lexical reference for each column you will substitute. In addition, you should assign an alias to each lexical reference. This enables you to use the same layout field and boilerplate label for whatever value you enter for the lexical reference on the Runtime Parameter Form.
If you use lexical references in your SELECT clause, you must specify the same number of items at runtime as were specified in the report's data model. Each value you specify for your lexical references at runtime must have the same datatype as its Initial Value.
If you use lexical references in your SELECT clause, the width of the column is derived from the Initial Value of the parameter. Consequently, you should ensure that the Initial Value of the parameter corresponds to the widest column that you intend to use.
A Report Builder link should not depend upon a lexical reference. That is, neither the child column of a link or its table name should be determined by a lexical reference. To achieve this functionality, you need to create a link with no columns specified and then enter the SQL clause (e.g., WHERE) for the link directly in the query. For example, your parent and child queries might be written as follows:
Parent Query: SELECT DEPTNO FROM EMP
Child Query: SELECT &PARM_1 COL_1,
&PARM2 COL_2 FROM EMP
WHERE &PARM_1 = :DEPTNO
Note how the WHERE clause makes a bind reference to DEPTNO, which was selected in the parent query. Also, this example assumes that you have created a link between the queries in the Data Model editor with no columns specified.
A lexical reference cannot be used to create additional bind variables after the After Form trigger fires. For example, suppose you have a query like the following (note that the WHERE clause is replaced by a lexical reference):
SELECT ENAME, SAL FROM EMP
&where_clause
If the value of the WHERE_CLAUSE parameter contains a reference to a bind variable, you must specify the value in the After Form trigger or earlier. You would get an error if you supplied the following value for the parameter in the Before Report trigger:
WHERE SAL = :new_bind
If you supplied this same value in the After Form trigger, the report would run.
Bind references
Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.
You create a bind reference by entering a colon (':') followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.
Bind references must not be the same name as any reserved SQL keywords. For more information, see the Oracle8 Server SQL Language Reference Manual.
Examples for Bind References
SELECT Clause
SELECT CUSTID, NVL(COMMPLAN, :DFLTCOMM) COMMPLAN FROM ORD
The value of DFLTCOMM replaces null values of COMMPLAN in the rows selected.
WHERE Clause
SELECT ORDID, TOTAL FROM ORD WHERE CUSTID = :CUST
The value of CUST is used to select a single customer.
GROUP BY Clause
SELECT NVL(COMMPLAN, :DFLTCOMM) COMMPLAN, SUM(TOTAL) TOTAL FROM ORD GROUP BY NVL(COMMPLAN, :DFLTCOMM)
All non-aggregate expressions such as NVL(COMMPLAN, :DFLTCOMM) in the SELECT clause must be replicated in the GROUP BY clause.
HAVING Clause
SELECT CUSTID, SUM(TOTAL) TOTAL FROM ORD GROUP BY CUSTID HAVING SUM(TOTAL) > :MINTOTAL
The value of MINTOTAL is used to select customers with a minimum total of orders.
ORDER BY Clause
SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL FROM ORD ORDER BY DECODE(:SORT, 1, SHIPDATE, 2, ORDERDATE)
The value of SORT is used to select either SHIPDATE or ORDERDATE as the sort criterion. Note that this is not the same as
ORDER BY 1 because :SORT is used as a value rather than to identify the position of an expression in the SELECT list. Note that DECODE is required in this example. You cannot use a bind variable in an ORDER BY clause unless it is with DECODE.
CONNECT BY and START WITH Clauses
References in CONNECT BY and START WITH clauses are used in the same way as they are in the WHERE and HAVING clauses.
PL/SQL
procedure double is begin; :my_param := :my_param*2; end;
The value of myparam is multiplied by two and assigned to myparam.
|
|
|
|
Goto Forum:
Current Time: Fri May 17 05:20:37 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
