469,623 Members | 1,192 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

Insert into and FETCH FIRST ROW ONLY

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.

Dec 14 '06 #1
15 10357
"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.
Dec 15 '06 #2
dataguy wrote:
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.
Is this what you are looking for?
INSERT INTO SESSION.TEMP SELECT * FROM T FETCH FIRST 10 ROWS ONLY

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Dec 15 '06 #3
When we tried to issue the statement similar to what you have below, we
kept getting an error complaining about the fetch. When we removed the
"fetch first xx rows only" and tried it, no problem.. I can post the
sql if you want it.
Serge Rielau wrote:
dataguy wrote:
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.
Is this what you are looking for?
INSERT INTO SESSION.TEMP SELECT * FROM T FETCH FIRST 10 ROWS ONLY

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Dec 15 '06 #4
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.
Dec 15 '06 #5

dataguy wrote:
When we tried to issue the statement similar to what you have below, we
kept getting an error complaining about the fetch. When we removed the
"fetch first xx rows only" and tried it, no problem.. I can post the
sql if you want it.
Serge Rielau wrote:
dataguy wrote:
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.
>
Is this what you are looking for?
INSERT INTO SESSION.TEMP SELECT * FROM T FETCH FIRST 10 ROWS ONLY

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Please post the SQL. It can be very helpful.

B.

Dec 15 '06 #6

dataguy wrote:
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.
Two questions:

1) If you remove the INSERT part of the statement, does the SELECT
statement execute without error?

2) What is the error returned?

B.

Dec 15 '06 #7
... which platform?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Dec 15 '06 #8
Db2 is OS/390, but we are doing it from stored procedure builder on the
client.
Serge Rielau wrote:
.. which platform?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Dec 15 '06 #9
We the select statement only and it works. Even if it was the select
statement, why would it work ok when I remove the Fetch statement?

As far as the error message, the person I'm working with has re-written
it to use a loop. It will take a bit of time to re-write it without.
I'll see if he can.
Brian Tkatch wrote:
dataguy wrote:
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.

Two questions:

1) If you remove the INSERT part of the statement, does the SELECT
statement execute without error?

2) What is the error returned?

B.
Dec 15 '06 #10
dataguy wrote:
Db2 is OS/390, but we are doing it from stored procedure builder on the
client.
Could be DB2 for zOS on your release doesn't have FFnR in subqueries yet.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Dec 15 '06 #11
The error message returned from stored procedure builder is as follows:

<LINE>'0DSNH199I E DSNHPARS LINE 374 COL 2 INVALID KEYWORD
"FETCH"; VALID SYMBOLS ARE: WITH UNION EXCEPT QUERYNO'</LINE>
Brian Tkatch wrote:
dataguy wrote:
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.

Two questions:

1) If you remove the INSERT part of the statement, does the SELECT
statement execute without error?

2) What is the error returned?

B.
Dec 15 '06 #12
DB2 for OS/390 doesn't support fetch for subselect nor fullselect even
Version 8.
FETCH is supported for select-statement.
Related part of syntax of INSERT statement is
INSERT INTO table-name|view-name fullselect isolation-clause QUERYNO
integer

DB2 for LUW support fetch for sub-select. So, fetch can be used in
INSERT.

Dec 16 '06 #13
If you are using DB2 for OS/390 V8. you can write
INSERT
INTO SESSION.NAM_TBL
WITH ISRT_DATA AS (
<your select statement>
)
SELECT A.*
FROM ISRT_DATA A
, LATERAL
(SELECT COUNT(*) RN
FROM ISRT_DATA B
WHERE B.PrimaryKey <= A.PrimaryKey
) AS BX
WHERE RN <= 70
WITH CS;

Dec 17 '06 #14
Thanks for the clarification
Tonkuma wrote:
DB2 for OS/390 doesn't support fetch for subselect nor fullselect even
Version 8.
FETCH is supported for select-statement.
Related part of syntax of INSERT statement is
INSERT INTO table-name|view-name fullselect isolation-clause QUERYNO
integer

DB2 for LUW support fetch for sub-select. So, fetch can be used in
INSERT.
Dec 18 '06 #15
Thanks to everyone who helped on this issue.
dataguy wrote:
Thanks for the clarification
Tonkuma wrote:
DB2 for OS/390 doesn't support fetch for subselect nor fullselect even
Version 8.
FETCH is supported for select-statement.
Related part of syntax of INSERT statement is
INSERT INTO table-name|view-name fullselect isolation-clause QUERYNO
integer

DB2 for LUW support fetch for sub-select. So, fetch can be used in
INSERT.
Dec 18 '06 #16

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Evan Smith | last post: by
2 posts views Thread by deebeetwo | last post: by
13 posts views Thread by RR | last post: by
5 posts views Thread by Bernd Hohmann | last post: by
14 posts views Thread by sangram.0149 | last post: by
3 posts views Thread by nymano | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.