Home » Developer & Programmer » Forms » Query with DD and month only.
Query with DD and month only. [message #618766] Tue, 15 July 2014 06:06 Go to next message
shahzad-ul-hasan
Messages: 275
Registered: August 2002
Senior Member
Dear All
Please View the attached file../fa/12026/0/
i want to find all thos record in execute query which is requal to in the DOB column.
Dob Column : Format mask: DD-MM
BUT when i ut 06-01 in DOb column in enter query mode. it gives me following messaage.
Frm-40301: Query Cuased no record to Retrive Re-Enter.

The actual data is stored in that format: 01-Jul-2007

Please advised...
  • Attachment: 223.JPG
    (Size: 61.19KB, Downloaded 83 times)

[Updated on: Tue, 15 July 2014 06:09]

Report message to a moderator

Re: Query with DD and month only. [message #618767 is a reply to message #618766] Tue, 15 July 2014 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not clear.
Do you mean all records with same DOB?
In this case, which DOB?

Re: Query with DD and month only. [message #618768 is a reply to message #618766] Tue, 15 July 2014 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ah, you modified your post when I was writing.

Quote:
BUT when i ut 06-01 in DOb column in enter query mode. it gives me following messaage.
Frm-40301: Query Cuased no record to Retrive Re-Enter.

Quote:
The actual data is stored in that format: 01-Jul-2007


Anyway the data is stored 06-01 <> 07-01

Now, are the data stored in a date column or a varchar2 one?

Re: Query with DD and month only. [message #618770 is a reply to message #618767] Tue, 15 July 2014 06:14 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 275
Registered: August 2002
Senior Member
No DOB means date of birth of the students.which is diffrent from one another.But When i put some DD-MM data like that: 01-06 it means those records which is matching the 01-06 showing...
The Data is stored in (Date) Data type..

[Updated on: Tue, 15 July 2014 06:17]

Report message to a moderator

Re: Query with DD and month only. [message #618775 is a reply to message #618770] Tue, 15 July 2014 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you need to get all rows where to_char(dob,'DD-MM') = <input value>

Re: Query with DD and month only. [message #618778 is a reply to message #618775] Tue, 15 July 2014 08:29 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 275
Registered: August 2002
Senior Member
yes
Re: Query with DD and month only. [message #618779 is a reply to message #618778] Tue, 15 July 2014 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Now, did you try to enter 07-01 or 01-07?

[Updated on: Tue, 15 July 2014 08:32]

Report message to a moderator

Re: Query with DD and month only. [message #618780 is a reply to message #618779] Tue, 15 July 2014 08:34 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 275
Registered: August 2002
Senior Member
yes
but it gives me
Frm-40301: Query Cuased no record to Retrive Re-Enter.
Re: Query with DD and month only. [message #618782 is a reply to message #618780] Tue, 15 July 2014 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For both?

Re: Query with DD and month only. [message #618794 is a reply to message #618782] Tue, 15 July 2014 11:44 Go to previous messageGo to next message
Littlefoot
Messages: 19623
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
An example based on Scott's EMP table (which contains the HIREDATE column (that would be your DOB column, right?)).

First of all, set the HIREDATE column to a non-database column (in its Property palette).

Then, create a PRE-QUERY trigger on the EMP block as
declare
  l_hire  varchar2(20);
  l_where varchar2(200);
begin	
  if :emp.hiredate is not null then
     -- convert entered date value into a character string, using the 'DD-MM' format mask
     l_hire := to_char(:emp.hiredate, 'dd-mm');
  
     -- create a WHERE clause for that block
     l_where := 'to_char(e.hiredate, ''dd-mm'') = ' || 
                '''' || l_hire || '''';

     -- set data block's ONETIME_WHERE property
     set_block_property('emp', onetime_where, l_where);
  end if;
end;  


Finally, you have to correctly populate HIREDATE non-database column with its value. In order to do that, use POST-QUERY trigger:
select hiredate 
  into :emp.hiredate
  from emp
  where empno = :emp.empno;


For example, I entered 17-12 into the HIREDATE column. After query has been executed, the result is

./fa/12027/0/
Re: Query with DD and month only. [message #618813 is a reply to message #618794] Tue, 15 July 2014 15:07 Go to previous messageGo to next message
Littlefoot
Messages: 19623
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Forgot to mention: why can't you leave the HIREDATE column a database column? Well, when I was trying to do what you asked, I couldn't make Forms fetch anything using HIREDATE in PRE-QUERY trigger. I did some tests, used MESSAGE built-in to display the ONETIME_WHERE clause, but - although everything looked fine, no records were returned.

I accept that I might have done it wrong - I just don't know what. If someone else has time to spare and create a working example based on Scott's schema, that would be nice.
Re: Query with DD and month only. [message #618818 is a reply to message #618813] Tue, 15 July 2014 21:54 Go to previous message
shahzad-ul-hasan
Messages: 275
Registered: August 2002
Senior Member
if i used 06-01% it will works fine...the all records shown which is equal to 06-01%.
Thanks for your help.
Previous Topic: Initial value should be dynamic based on the available list element months
Next Topic: FRM-40039
Goto Forum:
  


Current Time: Fri Sep 19 15:03:29 CDT 2014

Total time taken to generate the page: 0.16231 seconds