Query with DD and month only. [message #618766] |
Tue, 15 July 2014 06:06 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shahzad-ul-hasan
Messages: 625 Registered: August 2002
|
Senior Member |
|
|
Dear All
Please View the attached file.![/forum/fa/12026/0/](/forum/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 1508 times)
[Updated on: Tue, 15 July 2014 06:09] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Re: Query with DD and month only. [message #618794 is a reply to message #618782] |
Tue, 15 July 2014 11:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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
|
|
|
|
|