× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



so... i have a merge statement that is constructed dynamically from a
mapping file that contains source and target columns with "user
supplied" sql statements as needed for each source/target column
handling.

Essentially i am left with a fairly large merge statement structured
as basic merge statement... (not actual statement below - just to
illustrate of the merge I'm doing)

MERGE INTO RECORDS AR
USING (VALUES (:hv_activity, :hv_description)
FOR :hv_nrows ROWS)
AS AC (ACTIVITY, DESCRIPTION)
ON (AR.ACTIVITY = AC.ACTIVITY)
WHEN MATCHED THEN UPDATE SET DESCRIPTION = AC.DESCRIPTION
WHEN NOT MATCHED THEN INSERT (ACTIVITY, DESCRIPTION)
VALUES (AC.ACTIVITY, AC.DESCRIPTION);

nonetheless, this all works great... until I throw in some "user
supplied" sql that utilizes a UDF to handle data manipulation on the
source date columns

I can assure you the merge statement and UDF are working correctly...
however my UDF has a "declare continue handler for sqlexception" in
which I believe that once it comes across so many bad source dates and
the exit handler is executed so many times, it is throwing the sql
20226 max number of stacked diagnostics areas has been exceeded, and
causing my merge statement to end prematurely. Though I do not want
to add "NOT ATOMIC CONTINUE ON SQLEXCEPTION" on my merge statement
because I do indeed want it to terminate with exception if there is
truly an error i need to know about.

The udf works great but not under this capacity... my gut is to make
changes to the UDF, but not really sure that is necessary or the best
place to start... below is the udf code...

Anyone got any suggestions?

create or replace function
lscnvtools/IsValidDate (i_dateField varchar(10)
,i_dateValue varchar(10)
,i_rowKey varchar(10)
,i_rowVal varchar(50)
)
returns char (1)
language sql
returns null on null input
modifies sql data
set option commit=*none, datfmt=*iso

begin

declare g_charDate date;

declare continue handler for sqlexception
begin
insert into cnverr (erpgmid
,errowkey
,errowval
,ersrccol
,ermsgtxt)
values ('CNVPFSQLR'
,i_rowKey
,i_rowVal
,i_dateField concat ' ' concat i_dateValue
,'Invalid Date Value');
return 'N';
end;

if i_dateValue is null then
return 'N';
end if;

set g_charDate = Date(i_dateValue);
return 'Y';

end

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.