Oracle Forms

How to base a block on a FROM CLAUSE to sort on a non database column

Home page

The trick is to have the block sorted on a non database column, a calculated pseudo-column in this case.

This block is sorted on the “Total” calculated column.

The query to achieve this result is the following:

SELECT

empno,

ename,

job,

sal,

comm,

NVL(sal,'0')+NVL(comm,'0') AS TOTAL

FROM

EMP

ORDER BY total

The fastest way to create this kind of block in Forms is to first create a view, then base the block on that view.

In a first step, this allows the Forms Block wizard to create the items for you.

Step 1 : create the temporary view

CREATE OR REPLACE VIEW v_emp_tot AS

SELECT

empno,

ename,

job,

sal,

comm,

NVL(sal,'0')+NVL(comm,'0') AS TOTAL

FROM

EMP

ORDER BY total

Step 2 : build the block on that view within the form Datablock wizard

Step 3 : edit the block properties to change the following:

· DATA_SOURCE_TYPE

Change

Table

to

FROM clause

· DATA_SOURCE_NAME

Change

EMP

To

SELECT

empno,

ename,

job,

sal,

comm,

NVL(sal,'0')+NVL(comm,'0') AS TOTAL

FROM

EMP

ORDER BY total

· In the Advanced database node, set the DML_DATA_TARGET_NAME property to EMP (*)

· Set the PRIMARY KEY property to TRUE on the EMPNO item.

· Also edit the TOTAL item property to set INSERT and UPDATE properties to FALSE
and Query Only property to TRUE

Step 4 : drop the temporary view

(*) This article was originally constructed with addition of 4 block-level triggers to overload the On-Lock, On-Insert, On-Update and On-Delete triggers.

Someone (his name is Sebastian) posted a comment on the blog to indicate that this step is not necessary.

The sample dialog

Download the CLAUSE_FROM.fmb Forms module (9.0.2)

Execute the following EMP sample creation script:

CREATE TABLE EMP

(

EMPNO NUMBER(4) NOT NULL,

ENAME VARCHAR2(10 BYTE) NOT NULL,

JOB VARCHAR2(9 BYTE) NULL,

MGR NUMBER(4) NULL,

HIREDATE DATE NULL,

SAL NUMBER(7,2) NULL,

COMM NUMBER(7,2) NULL,

DEPTNO NUMBER(2) NULL

)

/

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7369, 'SMITH', 'CLERK', 7902, TO_Date( '12/17/1980 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 915, 10, 20);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7499, 'ALLEN', 'SALESMAN', 7698, TO_Date( '02/20/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 1600, 300, 30);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7521, 'WARD', 'SALESMAN', 7698, TO_Date( '02/22/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 1250, 500, 30);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7566, 'JONES', 'MANAGER', 7839, TO_Date( '04/02/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 2975, NULL, 20);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7654, 'MARTIN', 'SALESMAN', 7698, TO_Date( '09/28/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 1250, 1400, 30);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7698, 'BLAKE', 'MANAGER', 7839, TO_Date( '05/01/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 2850, NULL, 30);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7782, 'CLARK', 'MANAGER', 7839, TO_Date( '06/09/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 2450, NULL, 10);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7788, 'SCOTT', 'ANALYST', 7566, TO_Date( '12/09/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 3000, NULL, 20);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7839, 'KING', 'PRESIDENT', NULL, TO_Date( '12/17/1980 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 5000, NULL, 10);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7844, 'TURNER', 'SALESMAN', 7698, TO_Date( '09/08/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 1500, 0, 30);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7876, 'ADAMS', 'CLERK', 7788, TO_Date( '01/12/1983 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 1100, NULL, 20);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7900, 'JAMES', 'CLERK', 7698, TO_Date( '12/03/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 950, NULL, 30);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7902, 'FORD', 'ANALYST', 7566, TO_Date( '12/03/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 3000, NULL, 20);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,

DEPTNO ) VALUES (

7934, 'MILLER', 'CLERK', 7782, TO_Date( '12/09/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')

, 1301, NULL, 10);

COMMIT

/