serach and redisplay form

Hello,

I have a form displaying the table scott.emp
Then there exist two Text Items
Hiredate_start and
Hiredate_end
where you enter the dates (format: dd.mm.yyyy)

and another Button named Go_And_Search.

If I press the Button Go_And_Search,
I'd like to clear the form scott.emp
and display in this form only the records
with a hiredate between Hiredate_start
and Hiredate_end.

If the Go_And_Search Button is pressed a
WHEN-BUTTON-PRESSED Trigger is started,
containing the name of a procedure call_hiredate_search.

In this Procedure I:

GO_BLOCK('EMP');
CLEAR_BLOCK(No_Validate);

...but then ..... I'm helpless

GO_BLOCK('EMP');
CLEAR_BLOCK(No_Validate);
...........
...........
after this command you need to write down a query and store it values into form text items. like this:

SELECT empno,ename,sal,deptno
INTO :emp.empno, :emp.ename, :emp.sal, :emp.deptno
FROM emp
WHERE hiredate BETWEEN hiredate_start AND hiredate_end;


In query emp is represented a block name of form not the alias name of table etc.

Generally when we do something like this, your emp fields are in a base table block "emp" and the date fields are in a control block. clicking the button would then

go_block('emp');
clear_block('emp'); --actually not necessary
execute_query;

In your emp block's pre-query trigger you can set the where clause of the block to use the control block dates by something similar to the following:


def_where := cgfk$lkup_qry.add_and(def_where)
|| 'hiredate >= to_date('''
||to_char(:emp.hiredate_start,'DD.MM.YYYY')||''',''DD.MM.YYYY'')';
def_where := cgfk$lkup_qry.add_and(def_where)
|| 'hiredate < to_date('''
||to_char(:emp.hiredate_end + 1,'DD.MM.YYYY')||''',''DD.MM.YYYY'')';

SET_BLOCK_PROPERTY('emp', DEFAULT_WHERE, def_where);

the cgfk$lkup_qry.add_and is simply a procedure that appends the existing def_where variable with ' AND ' if necessary.

YOur suggestion does not work, it ends up with FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-01422.

By the way, I think it has to be
....
WHERE hiredate BETWEEN :block14.hiredate_start AND :block14.hiredate_end

enclose the complete query into BEGIN ..... END block

BEGIN

<write down your code here>

EXCEPTION
WHEN NO_DATE FOUND THEN
NULL;
END;

It looks as if shafique's method will only work if you are returning a single row to a non-database block. You would have to write a lot more code to narrow your search, navigate through records and update these records. It depends on what you are trying to do. I personally try to use Form's natural abilities as much as possible.

My previous option may be difficult to implement, i found another option in form that is go to block property, in 'database' option select WHERE option and type the following command 'hiredate between :hiredate_start and :hiredate_end'

-now run your form
-click on enter query buuton (i,e provided by oracle form by default)
-enter starting and ending date
-click on execute query button (i,e provided by oracle form by default)

Hello together,

thanks for your advices. I've done it now a little bit more flexibel
(with the scott.dept table and the text item dept_start and dept_stop and a push button; these three items are placed in a separate block named suche):

- dept_stop has a WHEN-VALIDATE-ITEM trigger to ensure dept_stop is
greater/equal than dept_start
- the Push Button named "Go" has a WHEN-BUTTON-PRESSED trigger containing: call_suche;
- call_suche:

PROCEDURE call_suche IS
BEGIN

IF (:suche.dept_start IS NULL AND :suche.dept_stop IS NULL)
THEN
SET_BLOCK_PROPERTY('DEPT', DEFAULT_WHERE, '');
EXECUTE_QUERY;

ELSIF (:suche.dept_start IS NOT NULL AND :suche.dept_stop IS NULL)
THEN
GO_BLOCK('DEPT');
CLEAR_BLOCK(No_Validate);
SET_BLOCK_PROPERTY('DEPT', DEFAULT_WHERE, 'deptno >= :suche.dept_start');
EXECUTE_QUERY;

ELSIF (:suche.dept_start IS NULL AND :suche.dept_stop IS NOT NULL)
THEN
GO_BLOCK('DEPT');
CLEAR_BLOCK(No_Validate);
SET_BLOCK_PROPERTY('DEPT', DEFAULT_WHERE, 'deptno <= :suche.dept_stop');
EXECUTE_QUERY;

ELSE
--(:suche.dept_start IS NOT NULL AND :suche.dept_stop IS NOT NULL)

GO_BLOCK('DEPT');
CLEAR_BLOCK(No_Validate);
SET_BLOCK_PROPERTY('DEPT', DEFAULT_WHERE, 'deptno BETWEEN :suche.dept_start AND :suche.dept_stop');
EXECUTE_QUERY;

NULL;
END IF;

END call_suche;

It works as follows:

-if dept_start is not empty (dept_stop is empty)--> display all records from scott.dept with
deptno >= dept_start.
-if dept_stop is not empty (dept_start is empty)--> display records with deptno <= dept_stop
-if dept_start and dept_stop is not empty--> display records with
deptno between dept_start and dept_stop
-if dept_start and dept_stop is empty--> display all records..