Lexical references are placeholders for columns or parameters 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
. Use a lexical reference when you want to
substitute multiple values at runtime.
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, as shown in the example below.
You create a lexical reference by typing 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 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.
Create your query containing lexical references.
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 data type 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 property 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 Reports 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 (for example,
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 view with no columns specified.
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.
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.
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.
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 runtime.
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.
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.
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.
Parameters in CONNECT BY
and START WITH
clauses are
used in the same way as they are in the WHERE
and HAVING
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 the following
at runtime for &WHEREORD
:
WHERE SAL > 1000 ORDER BY DEPTNO
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 data type 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.
Differences between bind and lexical references
Copyright © 1984, 2005, Oracle. All rights reserved.