Re: non alpha characters cleaning strings ??
Sorry to waste your all time.
I just found out that our RETARDED GODDAM Director of Development has a standard to NOT use Informix Stored procedures.
I can't even believe my ears. Most of our code is written in esqlc yet she wants to avoid any database specific languguages.
I am so pisssed.
If I ever ask another stored procedure question again, please find me an kill me. Arrrrghhhhhh.
Thanks for the vent.
========================
-<<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: Friday, March 30, 2007 10:07:55 AM
Subject: Re: non alpha characters cleaning strings ??
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 easil
>> "Floyd Wellershaus" <fwellers@yahoo.com> wrote in message >> news:mailman.509.1175272557.10648.informix-list@iiug.org... >> Sorry to waste your all time. >> I just found out that our RETARDED GODDAM Director of Development has a >> standard ... Is she an avid reader of cdi ...?
On 30 Mar, 17:35, Floyd Wellershaus <fwell...@yahoo.com> wrote:
> Sorry to waste your all time.
> I just found out that our RETARDED GODDAM Director of Development has a standard to NOT use Informix Stored procedures.
> I can't even believe my ears. Most of our code is written in esqlc yet she wants to avoid any database specific languguages.
>
> I am so pisssed.
> If I ever ask another stored procedure question again, please find me an kill me. Arrrrghhhhhh.
>
> Thanks for the vent.
>
> ========================
> -<<Floyd Wellershaus>>-
> Database Administrator
> Unix Administrator
>
> email: fwell...@yahoo.com
>
> Home: 703-430-0805
>
> Cell: 703-477-6045
> ========================
>
> http://www.one.org/
>
>
>
> ----- Original Message ----
> From: Doug Lawry <l...@nildram.co.uk>
> To: informix-l...@iiug.org
> Sent: Friday, March 30, 2007 10:07:55 AM
> Subject: Re: non alpha characters cleaning strings ??
>
> The following is syntactically corrected but untested :-)
> --
> Regards,
> Doug Lawrywww.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" <fwell...@yahoo.com> wrote in messagenews: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: fwell...@yahoo.com
> Home: 703-430-0805
> Cell: 703-477-6045
> ========================
>
> http://www.one.org/
>
> ----- Original Message ----
> From: Doug Lawry <l...@nildram.co.uk>
> To: Floyd Wellershaus <fwell...@yahoo.com>
> Cc: informix-l...@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 Lawrywww.douglawry.webhop.org
>
> ________________________________
>
> From: Floyd Wellershaus [mailto:fwell...@yahoo.com]
> Sent: 29 March 2007 21:05
> To: Doug Lawry; informix-l...@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