Forms 6i date search

[Avaatar]

Hello all,
i have at the moment following problem:
I have a record block, which is filled with a FROM clausel. In this Record Block is a field, which contains a date. The user can type in a date, to find all records from this date. Now i would like to modify this, so that Forms bring out all records >= the date the user entered, instead of the exact date the user entered.
How can i tell Forms to do this please?

Thank you.

Helge.

 

[lewisp]

One way of doing this is to make your date field a CHAR type. Set the query length of the field at least 6 characters larger than the actual length. Then in a pre_query trigger do this:

[tt]IF :block.date_field IS NOT NULL
THEN
:block.date_field := '#>= ''' || :block.date_field || '''';
END IF;[/tt]

There are, of course, other ways to acheive this by using SET_BLOCK_PROPERTY(...,DEFAULT_WHERE), but the above method works once for each query.

 

[Avaatar]

Thank you, it worked very well

 

[Avaatar]

Another question please,
i am experiencing now a heavy performance hit(i think because of the casting varchar2 -> date). How can i do this without a casting?

 

[lewisp]

On a PRE-QUERY trigger use this:

[tt]IF :block.date_field IS NOT NULL
THEN
Set_Block_Property('<block>',DEFAULT_WHERE,'date_field >= ''' || :block.date_field || '''');
:block.date_field := null;
ELSE
Set_Block_Property('<block>',DEFAULT_WHERE,'');
END IF;[/tt]

Of course, this is assuming your block does not have a WHERE clause defined. If it does you will have to use the correct setting in the set_block_property calls.

 

[Avaatar]

Thank you

 

[Avaatar]

Thank you for your help

 

[sem]

I think that Forms is robust enough to apply >= condition correctly without any need to use #, thus you may either allow a user to enter such criteria (e.g. type >=01.01.2004 directly into a field in Enter-Query mode) or use Lewis advice, but 1) without extra quotes; 2) with explicit conversion and COPY built-in.

IF :block.date_field IS NOT NULL
THEN
  COPY ('>=' || to_char(:block.date_field, get_item_property('block.date_field', format_mask) , 'block.date_field');
END IF;

Regards, Dima

http://seminihin.narod.ru

 

[sem]

Oops, missed right parenthesis after format_mask.

Regards, Dima

http://seminihin.narod.ru

 

[Avaatar]

Hello,
one(late) question please again:
I didnt noticed it before really, but what does the # do in the query you suggested?

IF :block.date_field IS NOT NULL
THEN
:block.date_field := '#>= ''' || :block.date_field || '''';
END IF;

Is it some type of implizit type conversion?

Thank you

 

[lewisp]

This is known as the 'hash-in' method. Its a carry over from forms 2.3, and forms knows to convert the # into the actual column name in the query. So if in your example the field in the form is called START_DATE, the query passed to the parser would become

[tt]SELECT <your columns> FROM <your table> WHERE START_DATE>= 'date value'[/tt]

 

[Avaatar]

Thank you
I still need to learn a lot it seems.