The SQL Query Statement property specifies a SQL SELECT statement that retrieves data from the database for your report. All features of the SELECT statement are supported, except the INTO and FOR UPDATE clauses. In building your SELECT statement, you can do the following:
Applies to |
Queries |
Value |
A valid SELECT statement not to exceed 64K. |
Default |
None |
Required/Optional |
Required |
Caution: If the width of a column in the database is increased after you have created your report, you need to update both the data model and the layout model to reflect the change. Otherwise, truncation may occur. Database columns are automatically updated when the query is re-parsed (for example, enter a space in SQL Query Statement and click OK). Summaries, formulas, and placeholders whose source columns have increased in width must have their own widths updated manually. Similarly, fields in the layout must be resized or have their sizing settings changed to accommodate the increased size of their source columns.
Following is an example of a SELECT statement:
SELECT O.CUSTID, P.DESCRIP, I.ITEMTOT, O.ORDERDATE, I.ORDID
FROM ORD O, PRODUCT P, ITEM I
WHERE O.ORDID = I.ORDID AND
I.PRODID = P.PRODID
ORDER BY O.CUSTID, P.PRODID, O.ORDERDATE
/* For each customer, show the products they bought */
/* (DESCRIP), the amount they spent on that product */
/* (ITEMTOT), the date they ordered the product */
/* (ORDERDATE),and the order id (ITEM.ORDID) of the */
/* order in which they bought the product. */
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)
Variables |
Description |
&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: RWRUN
REP SCOTT/TIGER NAME='SM%' |
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
Variables |
Description |
&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. |
Copyright © 1984, 2005, Oracle. All rights reserved.