Re: non alpha characters cleaning strings ??
First draft, with one section being lost in:
** first create the remove alpha procedure.
CREATE PROCEDURE rm_nonalpha(p_char varchar(50,1))
returning varchar(50,1); define i, mlen smallint;
define my_return varchar(50,1);
let my_return = "";
let mlen = length(p_char);
for i = 1 to mlen
if substr(p_char,i,1) matches "[a-zA-Z]" then
let my_return = my_return || substr(p_char,i,1);
end if;
end for;
return my_return;
END PROCEDURE;
** now the other one.
CREATE PROCEDURE update_hsssnxref (intab varchar(10))
DEFINE dst_ttsublog_token int; DEFINE dst_ssn char(9);
DEFINE dst_hs_code char(6);
DEFINE dst_first_name char(20);
DEFINE dst_initial char(1);
DEFINE dst_last_name char(20);
DEFINE dst_birth_dt date;
DEFINE dst_first_name_s char(20);
DEFINE dst_first_name_fst3_s varchar(3);
DEFINE dst_last_name_s char(20);
DEFINE dst_diploma_period varchar(80);
FOREACH getsource_cursor FOR
SELECT trim(ssn),trim(substr(trim(requester_return),1,6)),rm_nonalpha(upper(first_name)),upper(initial),
rm)nonalpha(upper(last_name)),birth_dt,rm_nonalpha(substr(upper(first_name),1,3))
FROM intab
WHERE ssn is not null
AND first_name is not null
AND last_name is not null
AND length(trim(requester_return)) >=6
AND rejectflag='N'
AND token=src_token
INTO dst_ssn,dst_first_name,dst_initial,dst_last_name,dst_birth_dt,dst_first_name_fst3_s;
here's where I'm lost now. I want to check for each row, if the combination key of ssn,hs_code
exists in the destination table. If it exists, then updat the row with all the other values.
If it doesn't exist then insert a new row.
END FOREACH;
END PROCEDURE;
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
----- Original Message ----
From: Doug Lawry <lawry@nildram.co.uk>
To: Floyd Wellershaus <fwellers@yahoo.com>
Cc: informix-list@iiug.org
Sent: Thursday, March 29, 2007 4:34:59 PM
Subject: RE: non alpha characters cleaning strings ??
Yes, this should be easy. Feel free to post your first draft or pseudo code,
and I'm sure we will try to out do each other providing a solution!
--
Regards,
Doug Lawry
www.douglawry.webhop.org
________________________________
From: Floyd Wellershaus [mailto:fwellers@yahoo.com]
Sent: 29 March 2007 21:05
To: Doug Lawry; informix-list@iiug.org
Subject: Re: non alpha characters cleaning strings ??
Pardon me for asking this newby question, but I don't have much experience
with stored procedures.
The reason for this data transformation to remove non-alpha characters is
because we have to map one table to another, on a continual basis
Basically, we'll be selecting everything from source-tab, manipulating the
data a little, with string commands, then for each row, determine if 2
columns ( key columns in the target-tab) already exist in the target tab. If
they don't exist, then insert the transformed row, if they do exist, then
update the row.
We've been trying to do this with Mercator ( now called websphere
transformation extender ).
We're running into issues with that, and I started thinking ( dangerous ),
that this sounds like something that can be handled efficiently with a
stored procedure.
Something like:
for each
select * from source-tab
newcol1=substr(oldcol1,1,6)
....
call another procedure or a different section of the same procedure to dothe alpha thing
.....
if newcol1 exists in target
then update
else insert
end
1) Am I on the right track here, is this something easily handled
efficiently with a stored procedure ?
2) And if so, can I get a basic sp script from someone to do this and
provide the framework I could use to make the real
procedure ?
Any advice is appreciated, and again, sorry for the newbie question. If I
had more time, I'd be glad to go to the books first, but I'm running out of
the little time I had for this project.
Thank you,
Floyd
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
----- Original Message ----
From: Doug Lawry <lawry@nildram.co.uk>
To: informix-list@iiug.org
Sent: Thursday, March 29, 2007 8:06:35 AM
Subject: Re: non alpha characters cleaning strings ??
This would be more elegant:
if substr(p_char,i,1) matches "[a-zA-Z]" then
--
Regards,
Doug Lawry
www.douglawry.webhop.org
"Ben" <ben_informix@comcast.net> wrote in message
news:mailman.454.1174400313.10648.informix-list@iiug.org...
Real Ugly but it works and maybe easier than installing the datablade:
create procedure rm_nonalpha(p_char varchar(50,1))
returning varchar(50,1);define i, mlen smallint;
define my_return varchar(50,1);
let my_return = "";
let mlen = length(p_char);
for i = 1 to mlen
if substr(p_char,i,1) in ("a", "b", "c", "d", "e", "f", "g", "h", "i",
"j", "k", "l", "m", "n", "o", "p", "q", "r",
"s", "t", "u", "v", "w", "x", "y", "z", "A",
"B", "C", "D", "E", "F", "G", "H", "I", "J",
"K", "L", "M", "N", "O", "P", "Q", "R", "S",
"T", "U", "V", "W", "X", "Y", "Z") then
let my_return = my_return || substr(p_char,i,1);
end if;
end for;
return my_return;
end procedure;
execute procedure rm_nonalpha("ab-cDe'fg ^hi\\jk@l#m%");
----- Original Message -----
From: Floyd Wellershaus
To: Obnoxio The Clown
Cc: informix-list@iiug.org
Sent: Saturday, March 17, 2007 12:29 PM
Subject: Re: non alpha characters cleaning strings ??
Hmm. I see the regexp.1.0. It is only for SunOS and Windows. Kinda leaves me
out
in the cold with AIX.
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
----- Original Message ----
From: Obnoxio The Clown <obnoxio@serendipita.com>
To: Floyd Wellershaus <fwellers@yahoo.com>
Cc: in
Floyd Wellershaus <fwellers@yahoo.com> schrieb: > here's where I'm lost now. I want to check for each row, if the combination key of ssn,hs_code > exists in the destination table. If it exists, then updat the row with all the other values. > If it doesn't exist then insert a new row. Is there a unique index on the combination key in the destination table? If so, then you could just try to insert the new row, and on exception -239 (Could not insert new row - duplicate value in a UNIQUE INDEX column) update the row. HTH, Richard
The following is syntactically corrected but untested :-)
--
Regards,
Doug Lawry
www.douglawry.webhop.org
CREATE PROCEDURE update_hsssnxref(){
Variable table name not directly supported:
(intab varchar(10))
}
DEFINE dst_ssn char(9);
DEFINE dst_hs_code char(6);
DEFINE dst_first_name char(20);
DEFINE dst_initial char(1);
DEFINE dst_last_name char(20);
DEFINE dst_birth_dt date;
DEFINE dst_first_name_fst3_s varchar(3);
{
Unused:
DEFINE dst_ttsublog_token int;
DEFINE dst_first_name_s char(20);
DEFINE dst_last_name_s char(20);
DEFINE dst_diploma_period varchar(80);
}
FOREACH
{
Only needed for WHERE CURRENT OF:
getsource_cursor FOR
}
SELECT trim(ssn),
trim(substr(trim(requester_return),1,6)),
rm_nonalpha(upper(first_name)),
upper(initial),
rm_nonalpha(upper(last_name)),
birth_dt,
rm_nonalpha(substr(upper(first_name),1,3))
INTO dst_ssn,
dst_hs_code,
dst_first_name,
dst_initial,
dst_last_name,
dst_birth_dt,
dst_first_name_fst3_s
FROM intab
WHERE ssn is not null
AND first_name is not null
AND last_name is not null
AND length(trim(requester_return)) >=6
AND rejectflag = 'N'
AND token = src_token
UPDATE outtab
SET first_name = dst_first_name,
initial = dst_initial,
last_name = dst_last_name,
birth_dt = dst_birth_dt,
first_name_fst3_s = dst_first_name_fst3_s
WHERE ssn = dst_ssn
AND hs_code = dst_hs_code;
IF DBINFO('sqlca.sqlerrd2') = 0 THEN
INSERT INTO outtab
(
ssn,
hs_code,
initial,
last_name,
birth_dt,
first_name_fst3_s
)
VALUES
(
dst_ssn,
dst_hs_code,
dst_initial,
dst_last_name,
dst_birth_dt,
dst_first_name_fst3_s
);
END IF
END FOREACH
END PROCEDURE;
"Floyd Wellershaus" <fwellers@yahoo.com> wrote in message
news:mailman.505.1175258713.10648.informix-list@iiug.org...
First draft, with one section being lost in:
** first create the remove alpha procedure.
CREATE PROCEDURE rm_nonalpha(p_char varchar(50,1))
returning varchar(50,1); define i, mlen smallint;
define my_return varchar(50,1);
let my_return = "";
let mlen = length(p_char);
for i = 1 to mlen
if substr(p_char,i,1) matches "[a-zA-Z]" then
let my_return = my_return || substr(p_char,i,1);
end if;
end for;
return my_return;
END PROCEDURE;
** now the other one.
CREATE PROCEDURE update_hsssnxref (intab varchar(10))
DEFINE dst_ttsublog_token int; DEFINE dst_ssn char(9);
DEFINE dst_hs_code char(6);
DEFINE dst_first_name char(20);
DEFINE dst_initial char(1);
DEFINE dst_last_name char(20);
DEFINE dst_birth_dt date;
DEFINE dst_first_name_s char(20);
DEFINE dst_first_name_fst3_s varchar(3);
DEFINE dst_last_name_s char(20);
DEFINE dst_diploma_period varchar(80);
FOREACH getsource_cursor FOR
SELECT
trim(ssn),trim(substr(trim(requester_return),1,6)),rm_nonalpha(upper(first_name)),upper(initial),
rm)nonalpha(upper(last_name)),birth_dt,rm_nonalpha(substr(upper(first_name),1,3))
FROM intab
WHERE ssn is not null
AND first_name is not null
AND last_name is not null
AND length(trim(requester_return)) >=6
AND rejectflag='N'
AND token=src_token
INTO
dst_ssn,dst_first_name,dst_initial,dst_last_name,dst_birth_dt,dst_first_name_fst3_s;
here's where I'm lost now. I want to check for each row, if the combination
key of ssn,hs_code
exists in the destination table. If it exists, then updat the row with all
the other values.
If it doesn't exist then insert a new row.
END FOREACH;
END PROCEDURE;
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
----- Original Message ----
From: Doug Lawry <lawry@nildram.co.uk>
To: Floyd Wellershaus <fwellers@yahoo.com>
Cc: informix-list@iiug.org
Sent: Thursday, March 29, 2007 4:34:59 PM
Subject: RE: non alpha characters cleaning strings ??
Yes, this should be easy. Feel free to post your first draft or pseudo code,
and I'm sure we will try to out do each other providing a solution!
--
Regards,
Doug Lawry
www.douglawry.webhop.org
________________________________
From: Floyd Wellershaus [mailto:fwellers@yahoo.com]
Sent: 29 March 2007 21:05
To: Doug Lawry; informix-list@iiug.org
Subject: Re: non alpha characters cleaning strings ??
Pardon me for asking this newby question, but I don't have much experience
with stored procedures.
The reason for this data transformation to remove non-alpha characters is
because we have to map one table to another, on a continual basis
Basically, we'll be selecting everything from source-tab, manipulating the
data a little, with string commands, then for each row, determine if 2
columns ( key columns in the target-tab) already exist in the target tab. If
they don't exist, then insert the transformed row, if they do exist, then
update the row.
We've been trying to do this with Mercator ( now called websphere
transformation extender ).
We're running into issues with that, and I started thinking ( dangerous ),
that this sounds like something that can be handled efficiently with a
stored procedure.
Something like:
for each
select * from source-tab
newcol1=substr(oldcol1,1,6)
....
call another procedure or a different section of the same procedure to dothe alpha thing
.....
if newcol1 exists in target
then update
else insert
end
1) Am I on the right track here, is this something easily handled
efficiently with a stored procedure ?
2) And if so, can I get a basic sp script from someone to do this and
provide the framework I could use to make the real
procedure ?
Any advice is appreciated, and again, sorry for the newbie question. If I
had more time, I'd be glad to go to the books first, but I'm running out of
the little time I had for this project.
Thank you,
Floyd
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
----- Original Message ----
From: Doug Lawry <lawry@nildram.co.uk>
To: informix-list@iiug.org
Sent: Thursday, March 29, 2007 8:06:35 AM
Subject: Re: non alpha characters cleaning strings ??
This would be more elegant:
if sub