ora-01847 when updating a record...please help
From: Simon <smuckers70_at_hotmail.com>
Date: 1 Jun 2004 05:27:08 -0700
Message-ID: <c0475b73.0406010427.57d80809_at_posting.google.com>
Hello,
and to_date(b.free_column9,'DD/MM/RR') =
(select min(to_date(c.free_column9,'DD/MM/RR')) from
emp_add_info_values_tab c
where c.add_info_type = 'B MED INS'
and c.free_column4 not in ('N','W')
and a.emp_no = c.emp_no
and to_date(c.free_column9,'DD/MM/RR') > to_date(a.free_column9,'DD/MM/RR')))
where exists(select b.free_column9
from emp_add_info_values_tab b
where a.add_info_type = 'B MED INS'
and to_date(b.free_column9,'DD/MM/RR') =
(select min(to_date(c.free_column9,'DD/MM/RR')) from
emp_add_info_values_tab c
where c.add_info_type = 'B MED INS'
and c.free_column4 not in ('N','W')
and a.emp_no = c.emp_no
and to_date(c.free_column9,'DD/MM/RR') > to_date(a.free_column9,'DD/MM/RR')))
Date: 1 Jun 2004 05:27:08 -0700
Message-ID: <c0475b73.0406010427.57d80809_at_posting.google.com>
Hello,
01/01/1901 01/01/1902 01/01/1902 01/01/1903 01/01/1903 etc......
I have got a bit lost in creating my update statement, but cannot understand why I am geting the following error message:-
ora-01847 day of month must be between 1 and last day of month.
update emp_add_info_values_tab a
set free_column10 = (select b.free_column9
from emp_add_info_values_tab b
where a.add_info_type = 'B MED INS'
and a.free_column4 not in ('N','W') and a.emp_no = b.emp_no and b.add_info_type = 'B MED INS'
and to_date(b.free_column9,'DD/MM/RR') =
(select min(to_date(c.free_column9,'DD/MM/RR')) from
emp_add_info_values_tab c
where c.add_info_type = 'B MED INS'
and c.free_column4 not in ('N','W')
and a.emp_no = c.emp_no
and to_date(c.free_column9,'DD/MM/RR') > to_date(a.free_column9,'DD/MM/RR')))
where exists(select b.free_column9
from emp_add_info_values_tab b
where a.add_info_type = 'B MED INS'
and a.free_column4 not in ('N','W') and a.emp_no = b.emp_no and b.add_info_type = 'B MED INS'
and to_date(b.free_column9,'DD/MM/RR') =
(select min(to_date(c.free_column9,'DD/MM/RR')) from
emp_add_info_values_tab c
where c.add_info_type = 'B MED INS'
and c.free_column4 not in ('N','W')
and a.emp_no = c.emp_no
and to_date(c.free_column9,'DD/MM/RR') > to_date(a.free_column9,'DD/MM/RR')))
Any help would be greatly appreciated...
Simon Received on Tue Jun 01 2004 - 14:27:08 CEST