Insert into and FETCH FIRST ROW ONLY

[dataguy]

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.

[Mark A]

Re: Insert into and FETCH FIRST ROW ONLY

"dataguy" <barry_noble@pr ogressive.comwr ote in message
news:1166133390 .288396.289010@ 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.

[Serge Rielau]

Re: Insert into and FETCH FIRST ROW ONLY

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/Forum2006/Forum2006.html

[dataguy]

Re: Insert into and FETCH FIRST ROW ONLY

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

[dataguy]

Re: Insert into and FETCH FIRST ROW ONLY

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" <barry_noble@pr ogressive.comwr ote in message
news:1166133390 .288396.289010@ 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.

[Brian Tkatch]

Re: Insert into and FETCH FIRST ROW ONLY


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.

[Brian Tkatch]

Re: Insert into and FETCH FIRST ROW ONLY


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_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" <barry_noble@pr ogressive.comwr ote in message
news:1166133390 .288396.289010@ 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.

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.

[Serge Rielau]

Re: Insert into and FETCH FIRST ROW ONLY

... which platform?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference

http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

[dataguy]

Re: Insert into and FETCH FIRST ROW ONLY

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

[dataguy]

Re: Insert into and FETCH FIRST ROW ONLY

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_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" <barry_noble@pr ogressive.comwr ote in message
news:1166133390 .288396.289010@ 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.

>
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.

[Serge Rielau]

Re: Insert into and FETCH FIRST ROW ONLY

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/Forum2006/Forum2006.html

[dataguy]

Re: Insert into and FETCH FIRST ROW ONLY

The error message returned from stored procedure builder is as follows:

<LINE>'0DSNH199 I 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_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" <barry_noble@pr ogressive.comwr ote in message
news:1166133390 .288396.289010@ 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.

>
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.

[Tonkuma]

Re: Insert into and FETCH FIRST ROW ONLY

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.

[Tonkuma]

Re: Insert into and FETCH FIRST ROW ONLY

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;

[dataguy]

Re: Insert into and FETCH FIRST ROW ONLY

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.