Subject | RE: Importing and parsing data in stored procedure |
---|---|
Author | Rick DeBay |
Post date | 2004-07-26T17:56:42Z |
Sorry to keep spamming the list, but I solved my problem. I had to
create another variable of type DATE, and assign the CAST to it:
DECLARE VARIABLE rxclaimnbr VARCHAR(15);
DECLARE VARIABLE clmseqnbr CHAR(3);
DECLARE VARIABLE claimsts CHAR(1);
DECLARE VARIABLE mbrbirthold VARCHAR(8);
DECLARE VARIABLE mbrbirthnew VARCHAR(10);
DECLARE VARIABLE newdate DATE;
begin
FOR SELECT RXCLAIMNBR,CLMSEQNBR,CLAIMSTS,MBRBIRTH
FROM X_CLAIM_DATES
INTO :rxclaimnbr,:clmseqnbr,:claimsts,:mbrbirthold
DO
BEGIN
IF (mbrbirthold NOT CONTAINING '000000') THEN
BEGIN
mbrbirthnew = SUBSTR(:mbrbirthold,1,4) || '-' ||
SUBSTR(:mbrbirthold,5,6) || '-' || SUBSTR(:mbrbirthold,7,8);
newdate = CAST(:mbrbirthnew AS DATE);
UPDATE CLAIMSRAW
SET MBRBIRTH=:newdate
WHERE RXCLAIMNBR=:rxclaimnbr AND CLMSEQNBR=:clmseqnbr AND
CLAIMSTS=:claimsts;
END
END
End
Now I just need to break out pieces as subroutines.
create another variable of type DATE, and assign the CAST to it:
DECLARE VARIABLE rxclaimnbr VARCHAR(15);
DECLARE VARIABLE clmseqnbr CHAR(3);
DECLARE VARIABLE claimsts CHAR(1);
DECLARE VARIABLE mbrbirthold VARCHAR(8);
DECLARE VARIABLE mbrbirthnew VARCHAR(10);
DECLARE VARIABLE newdate DATE;
begin
FOR SELECT RXCLAIMNBR,CLMSEQNBR,CLAIMSTS,MBRBIRTH
FROM X_CLAIM_DATES
INTO :rxclaimnbr,:clmseqnbr,:claimsts,:mbrbirthold
DO
BEGIN
IF (mbrbirthold NOT CONTAINING '000000') THEN
BEGIN
mbrbirthnew = SUBSTR(:mbrbirthold,1,4) || '-' ||
SUBSTR(:mbrbirthold,5,6) || '-' || SUBSTR(:mbrbirthold,7,8);
newdate = CAST(:mbrbirthnew AS DATE);
UPDATE CLAIMSRAW
SET MBRBIRTH=:newdate
WHERE RXCLAIMNBR=:rxclaimnbr AND CLMSEQNBR=:clmseqnbr AND
CLAIMSTS=:claimsts;
END
END
End
Now I just need to break out pieces as subroutines.