How do I ignore row giving error? [message #677666] |
Fri, 04 October 2019 02:01 |
Buchas
Messages: 101 Registered: March 2006
|
Senior Member |
|
|
Hello,
There is a select that gives error: "ORA-01841: (full) year must be between -4713 and +9999, and not be 0".
The select is this:
select
t_pas_msg_rec(
msg2.msgid,
msg2.receiver,
extract(second from msg2.created_ts - edc.created_ts))
bulk collect into
g_send_msg_data
from
pas_sepa_trans_references str,
pas_sepa_group_header sgh1,
pas_sepa_transactions stn1,
pas_sepa_group_header sgh2,
pas_sepa_transactions stn2,
pas_messages msg1,
inq_edoc edc,
(select
mes.msgid,
mes.receiver,
edc.created_ts
from
pas_messages mes,
inq_edoc edc
where
mes.msgtype = 'IFCCTRNS' and
mes.actl_msg_status = 'SWIFTsaug' and
mes.system = 'LITAS-INST' and
mes.sender = 'CTX_LB_BIC' and
mes.edoc_id = edc.edoc_id
and mes.msgs_date between sysdate - l_observ_interval and sysdate and
mes.msgs_date + 1/24 >= l_financial_time
) msg2
where
str.ref_type = 'Persiuntimas' and
str.from_id = stn1.id and
sgh1.id = stn1.sgh_id and
str.to_id = stn2.id and
sgh2.id = stn2.sgh_id and
sgh2.grp_hdr_msg_id = msg1.msgid and
msg1.edoc_id = edc.edoc_id and
sgh1.grp_hdr_msg_id = msg2.msgid;
Unfortunately I do not have access to the database the error is encountered (it is production environment) and I cannot reproduce it in development environment. If I cannot reproduce, I cannot fix it. Now the management says "just ignore the error and let the query retrieve the other rows". But how do I do this? E.g. if I have a query
SELECT ROWNUM / (rownum-1) FROM DUAL CONNECT BY LEVEL <= 5
that gives "ORA-01476: divisor is equal to zero" error, can I ignore it and display the other 4 rows?
|
|
|
|
|
Re: How do I ignore row giving error? [message #677671 is a reply to message #677669] |
Fri, 04 October 2019 04:00 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Re-run the query with all references to one of those columns removed.
Do it again with another column removed.
Keep going until the error goes away.
That should tell you which one is the problem.
Then you are going to have to find the invalid data in that column and fix it.
|
|
|