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:
n 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.
n Create
your query containing lexical references.
Restriction on Lexical Reference
n You
cannot make lexical references in a PL/SQL statement.
n If
a column or parameter is used as a lexical reference in a query, its Datatype
must be Character.
n 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.
n 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.
n 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.
n 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
n 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.
n 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.
Initial Value
Description The Initial Value property is the default
value for the parameter. This value is
used unless it is overridden on the command line or the Runtime Parameter Form. Enter any value appropriate for the Datatype
of the parameter. Initial Value cannot
exceed 1K in length. If Initial Value is
left blank, the parameter has no default value.
Applies to parameters
Required/Optional optional
Default For DESTYPE, Screen. For DESFORMAT, dflt. For COPIES, 1. For all other parameters, blank.
Lexical reference examples
<> Related Topics <> All 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.
SQL Query statement with bind and
lexical references example
<> Related Topics <> All Examples
SQL Query statement with bind and lexical references example
Following is an example of a
SELECT statement that uses bind and lexical references:
SELECT CUSTID, SUM(TOTAL) TOTAL
FROM &FROMGROUP
HAVING SUM(TOTAL) > :MINTOTAL
where:
&FROMGROUP Is ORD GROUP BY CUSTID (or some other
value that you enter at runtime). Note
that, in this case, you must define a parameter named FROMGROUP with an Initial
Value specified. If the value of
&FROMGROUP is null when the SELECT statement is parsed, you will get an
error because the statement has no table name after FROM.
:MINTOTAL Is a column from another query that is used to select customers
with a minimum total of orders.
SQL Query statement with lexical reference example
Following is an example of a
SELECT statement that uses a lexical reference to a parameter:
SELECT ENAME, EMPNO
FROM EMP
WHERE ENAME LIKE UPPER
(&NAME)
where:
&NAME Refers to a parameter with a default
value of 'JO%'. Note that you can also
specify a value for the parameter at runtime.
For example, from the command line, you could type:
No comments:
Post a Comment