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_N BR AS PolicySuffix,
PDTL.POL_STRT_D T AS PolicyStartDate ,
PDTL.POL_END_DT AS PolicyEndDate,
PDTL.POL_SRCE_C D AS PolicySourcecd,
PDTL.POL_ST_CD AS PolicyStateCd,
PDTL.PROD_CTGRY _CD AS ProductCategory Cd,
ADRS.PRTY_STR_N AM AS StreetName,
ADRS.PRTY_CITY_ NAM AS CityName,
ADRS.PRTY_ST_CD AS StateCd,
ADRS.PRTY_ZIP_C D AS ZipCd,
ADRS.PRTY_ZIP_C D_EXTN AS ZipCdExtn,
PRTY.PRTY_TYP_C D AS PartyTypeCd,
PRTY.PRTY_BRTH_ DT AS PartyDOB,
PRTY.PRTY_BSNS_ IND AS PartyBusinessIn d,
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_N AM LIKE W_SRCH_LAST_NAM
AND PRTY.PRTY_FRST_ NAM LIKE W_SRCH_FRST_NAM
AND (
(PRTY.PROC_EFF_ TS <= CURRENT_TIMESTA MP
AND
PRTY.PROC_EXPR_ TS CURRENT_TIMESTA MP
AND
PRTY.ITR_EFF_DT <= W_REF_DATE
)
OR
(PRTY.PROC_EXPR _TS <= CURRENT_TIMESTA MP
AND
PRTY.ITR_EFF_DT <= W_REF_DATE
AND
PRTY.ITR_EXPR_D T W_REF_DATE
)
)
--
-- APH_POL_DTL PREDICATES
--
AND (
(PDTL.PROC_EFF_ TS <= CURRENT_TIMESTA MP
AND
CASE WHEN PDTL.PROC_EXPR_ DT = W_MAX_DATE
THEN TIMESTAMP (PDTL.POL_END_D T, PDTL.PROC_EXPR_ TM)
ELSE PDTL.PROC_EXPR_ TS
END
CURRENT_TIMESTA MP
AND
PDTL.ITR_EFF_DT <= W_REF_DATE
)
OR
(CASE WHEN PDTL.PROC_EXPR_ DT = W_MAX_DATE
THEN TIMESTAMP (PDTL.POL_END_D T, PDTL.PROC_EXPR_ TM)
ELSE PDTL.PROC_EXPR_ TS
END
<= CURRENT_TIMESTA MP
AND
PDTL.ITR_EFF_DT <= W_REF_DATE
AND
CASE WHEN PDTL.ITR_EXPR_D T = W_MAX_DATE
THEN PDTL.POL_END_DT
ELSE PDTL.ITR_EXPR_D T
END
W_REF_DATE
)
)
--
-- SUB SELECT TO RETRIEVE ONLY ONE APH_POL_DTL ROW
--
AND PDTL.PROC_EFF_T S =
(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_TIMESTA MP
AND
PDTL2.PROC_EXPR _TS CURRENT_TIMESTA MP
AND
PDTL2.ITR_EFF_D T <= W_REF_DATE
)
OR
(PDTL2.PROC_EXP R_TS <= CURRENT_TIMESTA MP
AND
PDTL2.ITR_EFF_D T <= W_REF_DATE
AND
PDTL2.ITR_EXPR_ DT W_REF_DATE
)
)
)
--
-- APH_ADRS PREDICATES
--
AND (
(ADRS.PROC_EFF_ TS <= CURRENT_TIMESTA MP
AND
ADRS.PROC_EXPR_ TS CURRENT_TIMESTA MP
AND
ADRS.ITR_EFF_DT <= W_REF_DATE
)
OR
(ADRS.PROC_EXPR _TS <= CURRENT_TIMESTA MP
AND
ADRS.ITR_EFF_DT <= W_REF_DATE
AND
ADRS.ITR_EXPR_D T W_REF_DATE
)
OR
(ADRS.PROC_EFF_ DT IS NULL
AND
ADRS.PROC_EFF_T M 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_D T IS NULL
)
)
--
-- SUB SELECT TO RETRIEVE ONLY ONE APH_ADRS ROW
--
AND ADRS.PROC_EFF_T S =
(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_TIMESTA MP
AND
ADRS2.PROC_EXPR _TS CURRENT_TIMESTA MP
AND
ADRS2.ITR_EFF_D T <= W_REF_DATE
)
OR
(ADRS2.PROC_EXP R_TS <= CURRENT_TIMESTA MP
AND
ADRS2.ITR_EFF_D T <= 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_D T IS NULL
AND
ADRS2.ITR_EXPR_ DT IS NULL
)
)
)
FETCH FIRST 70 ROWS ONLY
WITH UR;
Mark A wrote:
"dataguy" <ba*********@pr ogressive.comwr ote in message
news:11******** **************@ j72g2000cwa.goo glegroups.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.
