A script-enabled browser is required for this page to function properly.

SQL Query Statement property

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

Restrictions

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.

Examples

Example 1:  SQL Query statement

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. */

Example 2:  SQL Query statement with lexical reference

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%'

Example 3:  SQL Query statement with bind and lexical references

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.