Basing a Block on a FROM Clause QUERY in Oracle D2k
This is a requirement when columns from multiple tables must be displayed in the block and the conditions for selecting the individual columns vary. Specifying a SELECT instead of a base table has the following advantages:
Although an explicit option in Forms 5.x and above, you could indirectly base a block on a FROM clause QUERY in Forms 4.5. In Forms 4.5, you specify a SELECT statement to substitute a table name as a base table (that is, in the FROM clause).
Specify a SELECT statement involving multiple table joins instead of a base table for a block, provided that the SELECT statement is specified within single parentheses. Column ambiguity is not automatically resolved. In Forms 4.5, enclose this SELECT statement within parentheses.
The Query Database Source columns have to be specified in the block’s Property Palette. This can be done by using the Data Block Wizard or by using the block Property Palette when creating the block manually.
The WHERE condition has to be specified properly and should be contained as part of the INLINE query instead of the DEFAULT_WHERE for the block, especially when selecting mutually non-related column sets and joining on non–foreign key columns. Consider the SELECT statement:
(SELECT DEPT.DEPTNO, DNAME, EMP.EMPNO, ENAME, JOB, HIREDATE, SAL
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO)
This SELECT should be specified as it is (in Forms 4.5, also) rather than specifying EMP as the base table with DNAME as a foreign key look-up column. (This is the second method referred to in Chapter 1 for querying by nonbase table items.)
Here is an additional tip that will help as you work with base tables based on a FROM clause query:
A FROM clause query specified as a base table for a data block is executed as an inline view and facilitates faster execution. Also, querying and ordering by foreign key look-up items are thus simplified, similar to querying and ordering in an ordinary base table block.