Here is the SQL in Question:
Note.. We tried putting an open and closed paran around the select
statements and it didn't help.
INSERT
INTO SESSION.NAM_TBL
SELECT PRNW.POL_ID_NBR AS PolicyNumber,
PRNW.RENW_SFX_NBR AS PolicySuffix,
PDTL.POL_STRT_DT AS PolicyStartDate,
PDTL.POL_END_DT AS PolicyEndDate,
PDTL.POL_SRCE_CD AS PolicySourcecd,
PDTL.POL_ST_CD AS PolicyStateCd,
PDTL.PROD_CTGRY_CD AS ProductCategoryCd,
ADRS.PRTY_STR_NAM AS StreetName,
ADRS.PRTY_CITY_NAM AS CityName,
ADRS.PRTY_ST_CD AS StateCd,
ADRS.PRTY_ZIP_CD AS ZipCd,
ADRS.PRTY_ZIP_CD_EXTN AS ZipCdExtn,
PRTY.PRTY_TYP_CD AS PartyTypeCd,
PRTY.PRTY_BRTH_DT AS PartyDOB,
PRTY.PRTY_BSNS_IND AS PartyBusinessInd,
PRTY.PRTY_NAM AS PartyName
-- RP910-PRTY-LST-NAM
-- RP910-PRTY-FRST-NAM
-- RP910-PRTY-NAM-INIT
-- RP910-PRTY-NAM-SFX
FROM PMTPSM_APH_PRTY PRTY
INNER JOIN PMTPSM_APH_POL_RNW PRNW
ON PRTY.POL_RENEW_KEY = PRNW.POL_RENEW_KEY
AND PRTY.PART_KEY = PRNW.PART_KEY
INNER JOIN PMTPSM_APH_POL_DTL PDTL
ON PRNW.POL_RENEW_KEY = PDTL.POL_RENEW_KEY
AND PRNW.PART_KEY = PDTL.PART_KEY
LEFT OUTER JOIN PMTPSM_APH_ADRS ADRS
ON PRNW.POL_RENEW_KEY = ADRS.POL_RENEW_KEY
AND PRNW.PART_KEY = ADRS.PART_KEY
--
-- NAME PREDICATES FOR APH_PRTY
--
WHERE PRTY.PRTY_LST_NAM LIKE W_SRCH_LAST_NAM
AND PRTY.PRTY_FRST_NAM LIKE W_SRCH_FRST_NAM
AND (
(PRTY.PROC_EFF_TS <= CURRENT_TIMESTAMP
AND
PRTY.PROC_EXPR_TS CURRENT_TIMESTAMP
AND
PRTY.ITR_EFF_DT <= W_REF_DATE
)
OR
(PRTY.PROC_EXPR_TS <= CURRENT_TIMESTAMP
AND
PRTY.ITR_EFF_DT <= W_REF_DATE
AND
PRTY.ITR_EXPR_DT W_REF_DATE
)
)
--
-- APH_POL_DTL PREDICATES
--
AND (
(PDTL.PROC_EFF_TS <= CURRENT_TIMESTAMP
AND
CASE WHEN PDTL.PROC_EXPR_DT = W_MAX_DATE
THEN TIMESTAMP (PDTL.POL_END_DT, PDTL.PROC_EXPR_TM)
ELSE PDTL.PROC_EXPR_TS
END
CURRENT_TIMESTAMP
AND
PDTL.ITR_EFF_DT <= W_REF_DATE
)
OR
(CASE WHEN PDTL.PROC_EXPR_DT = W_MAX_DATE
THEN TIMESTAMP (PDTL.POL_END_DT, PDTL.PROC_EXPR_TM)
ELSE PDTL.PROC_EXPR_TS
END
<= CURRENT_TIMESTAMP
AND
PDTL.ITR_EFF_DT <= W_REF_DATE
AND
CASE WHEN PDTL.ITR_EXPR_DT = W_MAX_DATE
THEN PDTL.POL_END_DT
ELSE PDTL.ITR_EXPR_DT
END
W_REF_DATE
)
)
--
-- SUB SELECT TO RETRIEVE ONLY ONE APH_POL_DTL ROW
--
AND PDTL.PROC_EFF_TS =
(SELECT MAX(PDTL2.PROC_EFF_TS)
FROM PMTPSM_APH_POL_DTL PDTL2
WHERE PDTL2.POL_RENEW_KEY = PDTL.POL_RENEW_KEY
AND PDTL2.PART_KEY = PDTL.PART_KEY
AND (
(PDTL2.PROC_EFF_TS <= CURRENT_TIMESTAMP
AND
PDTL2.PROC_EXPR_TS CURRENT_TIMESTAMP
AND
PDTL2.ITR_EFF_DT <= W_REF_DATE
)
OR
(PDTL2.PROC_EXPR_TS <= CURRENT_TIMESTAMP
AND
PDTL2.ITR_EFF_DT <= W_REF_DATE
AND
PDTL2.ITR_EXPR_DT W_REF_DATE
)
)
)
--
-- APH_ADRS PREDICATES
--
AND (
(ADRS.PROC_EFF_TS <= CURRENT_TIMESTAMP
AND
ADRS.PROC_EXPR_TS CURRENT_TIMESTAMP
AND
ADRS.ITR_EFF_DT <= W_REF_DATE
)
OR
(ADRS.PROC_EXPR_TS <= CURRENT_TIMESTAMP
AND
ADRS.ITR_EFF_DT <= W_REF_DATE
AND
ADRS.ITR_EXPR_DT W_REF_DATE
)
OR
(ADRS.PROC_EFF_DT IS NULL
AND
ADRS.PROC_EFF_TM IS NULL
AND
ADRS.PROC_EXPR_DT IS NULL
AND
ADRS.PROC_EXPR_TM IS NULL
AND
ADRS.ITR_EFF_DT IS NULL
AND
ADRS.ITR_EXPR_DT IS NULL
)
)
--
-- SUB SELECT TO RETRIEVE ONLY ONE APH_ADRS ROW
--
AND ADRS.PROC_EFF_TS =
(SELECT MAX(ADRS2.PROC_EFF_TS)
FROM PMTPSM_APH_ADRS ADRS2
WHERE ADRS2.POL_RENEW_KEY = ADRS.POL_RENEW_KEY
AND ADRS2.PART_KEY = ADRS.PART_KEY
AND (
(ADRS2.PROC_EFF_TS <= CURRENT_TIMESTAMP
AND
ADRS2.PROC_EXPR_TS CURRENT_TIMESTAMP
AND
ADRS2.ITR_EFF_DT <= W_REF_DATE
)
OR
(ADRS2.PROC_EXPR_TS <= CURRENT_TIMESTAMP
AND
ADRS2.ITR_EFF_DT <= W_REF_DATE
AND
ADRS2.ITR_EXPR_DT W_REF_DATE
)
OR
(ADRS2.PROC_EFF_DT IS NULL
AND
ADRS2.PROC_EFF_TM IS NULL
AND
ADRS2.PROC_EXPR_DT IS NULL
AND
ADRS2.PROC_EXPR_TM IS NULL
AND
ADRS2.ITR_EFF_DT IS NULL
AND
ADRS2.ITR_EXPR_DT IS NULL
)
)
)
FETCH FIRST 70 ROWS ONLY
WITH UR;
Mark A wrote:
"dataguy" <ba*********@progressive.comwrote in message
news:11**********************@j72g2000cwa.googlegr oups.com...
I am trying to insert into a temporary table but only the first n
number of rows. I thought I could use the combination of insert into
and fect first row command ,but it won't work. Does anyone know why?
Any other suggestions other than writing a loop? This is in a db2 sql
stored procedure that is called from another db2 sql stored proc.
If you post the SQL, then maybe someone will be able to help you.
