I left P_CONVERT_DATES running overnight (15 hrs) on my laptop and it
still hadn't completed. I'm trying to import and convert dates from
varchar to date or time. Claimsraw has 83919 rows and no indexes,
claimspaidreversed has 102153 rows and the PK
(RXCLAIMNBR,CLMSEQNBR,CLAIMSTS). X_claim_dates has 82028 rows.
The SP P_CONVERT_DATES loops through all rows in x_claim_dates, converts
the strings to dates and times using P_CVRT_YYYMMDD and P_CVRT_HHMMSS,
and updates the two tables.
Am I doing anything incorrectly or making performance mistakes?

SET TERM ^^ ;
ALTER PROCEDURE P_CONVERT_DATES
AS
DECLARE VARIABLE rxclaimnbr VARCHAR(15);
DECLARE VARIABLE clmseqnbr CHAR(3);
DECLARE VARIABLE claimsts CHAR(1);
DECLARE VARIABLE timesbm VARCHAR(6);
DECLARE VARIABLE t_timesbm TIME;
DECLARE VARIABLE mbrbirth VARCHAR(8);
DECLARE VARIABLE d_mbrbirth DATE;
DECLARE VARIABLE birthdte VARCHAR(8);
DECLARE VARIABLE d_birthdte DATE;
DECLARE VARIABLE orgpdsbmdt VARCHAR(8);
DECLARE VARIABLE d_orgpdsbmdt DATE;
DECLARE VARIABLE rvdatesbm VARCHAR(8);
DECLARE VARIABLE d_rvdatesbm DATE;
DECLARE VARIABLE writtendte VARCHAR(8);
DECLARE VARIABLE d_writtendte DATE;
DECLARE VARIABLE dteprvfil1 VARCHAR(8);
DECLARE VARIABLE d_dteprvfil1 DATE;
DECLARE VARIABLE dteprvfil2 VARCHAR(8);
DECLARE VARIABLE d_dteprvfil2 DATE;
DECLARE VARIABLE dteprvfil3 VARCHAR(8);
DECLARE VARIABLE d_dteprvfil3 DATE;
DECLARE VARIABLE dteinjury VARCHAR(8);
DECLARE VARIABLE d_dteinjury DATE;
begin
FOR SELECT
RXCLAIMNBR,CLMSEQNBR,CLAIMSTS,MBRBIRTH,BIRTHDTE,TIMESBM,ORGPDSBMDT,RVDAT
ESBM,WRITTENDTE,DTEPRVFIL1,DTEPRVFIL2,DTEPRVFIL3,DTEINJURY
FROM X_CLAIM_DATES
INTO
:rxclaimnbr,:clmseqnbr,:claimsts,:mbrbirth,:birthdte,:timesbm,:orgpdsbmd
t,:rvdatesbm,:writtendte,:dteprvfil1,:dteprvfil2,:dteprvfil3,:dteinjury
DO
BEGIN
EXECUTE PROCEDURE P_CVRT_YYYYMMDD(:mbrbirth)
RETURNING_VALUES(:d_mbrbirth);
EXECUTE PROCEDURE P_CVRT_YYYYMMDD(:birthdte)
RETURNING_VALUES(:d_birthdte);
EXECUTE PROCEDURE P_CVRT_HHMMSS(:timesbm)
RETURNING_VALUES(:t_timesbm);
EXECUTE PROCEDURE P_CVRT_YYYYMMDD(:orgpdsbmdt)
RETURNING_VALUES(:d_orgpdsbmdt);
EXECUTE PROCEDURE P_CVRT_YYYYMMDD(:rvdatesbm)
RETURNING_VALUES(:d_rvdatesbm);
EXECUTE PROCEDURE P_CVRT_YYYYMMDD(:writtendte)
RETURNING_VALUES(:d_writtendte);
EXECUTE PROCEDURE P_CVRT_YYYYMMDD(:dteprvfil1)
RETURNING_VALUES(:d_dteprvfil1);
EXECUTE PROCEDURE P_CVRT_YYYYMMDD(:dteprvfil2)
RETURNING_VALUES(:d_dteprvfil2);
EXECUTE PROCEDURE P_CVRT_YYYYMMDD(:dteprvfil3)
RETURNING_VALUES(:d_dteprvfil3);
EXECUTE PROCEDURE P_CVRT_YYYYMMDD(:dteinjury)
RETURNING_VALUES(:d_dteinjury);
UPDATE CLAIMSRAW
SET MBRBIRTH=:d_mbrbirth,
BIRTHDTE=:d_birthdte,
TIMESBM=:t_timesbm,
ORGPDSBMDT=:d_orgpdsbmdt,
RVDATESBM=:d_rvdatesbm,
WRITTENDTE=:d_writtendte,
DTEPRVFIL1=:d_dteprvfil1,
DTEPRVFIL2=:d_dteprvfil2,
DTEPRVFIL3=:d_dteprvfil3,
DTEINJURY=:d_dteinjury
WHERE RXCLAIMNBR=:rxclaimnbr AND CLMSEQNBR=:clmseqnbr AND
CLAIMSTS=:claimsts;
UPDATE CLAIMSPAIDREVERSED
SET MBRBIRTH=:d_mbrbirth,
BIRTHDTE=:d_birthdte,
TIMESBM=:t_timesbm,
ORGPDSBMDT=:d_orgpdsbmdt,
RVDATESBM=:d_rvdatesbm,
WRITTENDTE=:d_writtendte,
DTEPRVFIL1=:d_dteprvfil1,
DTEPRVFIL2=:d_dteprvfil2,
DTEPRVFIL3=:d_dteprvfil3,
DTEINJURY=:d_dteinjury
WHERE RXCLAIMNBR=:rxclaimnbr AND CLMSEQNBR=:clmseqnbr AND
CLAIMSTS=:claimsts;
END
end
^^

ALTER PROCEDURE P_CVRT_HHMMSS (
HHMMSS VarChar(6))
returns (
T_TIME Time)
AS
begin
IF (hhmmss NOT CONTAINING '000000') THEN
BEGIN
t_time = CAST((SUBSTR(:hhmmss,1,2) || ':' || SUBSTR(:hhmmss,3,4)
|| ':' || SUBSTR(:hhmmss,5,6)) AS TIME);
END
ELSE
BEGIN
t_time = NULL;
END
end
^^

ALTER PROCEDURE P_CVRT_YYYYMMDD (
YYYYMMDD VarChar(8))
returns (
DT_DATE Date)
AS
begin
IF (yyyymmdd NOT CONTAINING '000000') THEN
BEGIN
dt_date = CAST((SUBSTR(:yyyymmdd,1,4) || '-' ||
SUBSTR(:yyyymmdd,5,6) || '-' || SUBSTR(:yyyymmdd,7,8)) AS DATE);
END
ELSE
BEGIN
dt_date = NULL;
END
end
^^
SET TERM ; ^^

Rick DeBay
Senior Software Developer
RxStrategies.net