Re: Query by Example of Date Items in SQL Forms 4.5

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1998/03/31
Message-ID: <6fra68$27h7@hendrix.csufresno.edu>#1/1

In article <35209EB2.2C88_at_sh.cvut.cz>,
Jan Januska <J.Januska_at_sh.cvut.cz> wrote:
>Hi!
>I have block in SQL Forms 4.5 that have date items using format like
>'WW/YY' and 'MMYY'.
>When quering by example these item, I have following problems:
>
>In first case ('WW/YY') always appears message:
> FRM-40358: Invalid date in example record. Query not issued.
>I thing that is due ORACLE (7.3) not fully supports format WW.
>What can I do?

It is also because WW cannot be converted back to a specific date. It looks like you are trying to query based on a range of dates-- you want any date that falls within the week.

>In second case ('MMYY') select only record having values exactly
>truncated to month (1th of month). I want select all record that match
>to giving month. How?

You will need to do some behind-the-scenes coding to get the form to do what you want. In the on-select trigger, use get_block_property to get the Default_Where clause. Then write your pl/sql code to parse the clause and interpret the values.

If you find a value in the MMYY field, remove that part of the where clause and replace it with the text:
  'trunc(Date_Col) Between to_date(''01MMYYYY'',''ddmmyyyy'')'

               ||' and to_date(''31MMYYYY'',''ddmmyyyy'')'

Replace the MMYYYY part of the to_date function with real numbers, and the 31 should be 28, 29, or 30, depending on month and year. Have fun getting all the apostrophe characters (') just right (use two in place of one everywhere except the first and last on each line).

Then replace the where clause using set_block_property, and then use the command select_records.

Good luck,
Steve Cosner

---

http://members.aol.com/stevec5088
Downloadable Quick Access utility form: Display and update any table. Received on Tue Mar 31 1998 - 00:00:00 CST