Maybe you need to do something like this;
Select A, B, C from ROBS_TABLE inner join lateral (select C from IBM UDTF where ?=??) X (C) on 1=1
Lateral keyword lets you used inline tables differently.
From ACS RSS examples:
--
-- category: DB2 for i Services
-- description: Interrogate interactive jobs
--
with INTERACTIVE_JOBS(JOBNAME, STATUS, CPU, IO)
as (select JOB_NAME, JOB_STATUS, CPU_TIME, TOTAL_DISK_IO_COUNT
from table(QSYS2.ACTIVE_JOB_INFO('YES', 'QINTER', '*ALL')) as A
where JOB_STATUS in ('LCKW', 'RUN'))
select JOBNAME, STATUS, CPU, IO, PROGRAM_LIBRARY_NAME, PROGRAM_NAME, MODULE_LIBRARY_NAME, MODULE_NAME, hex(bigint(STATEMENT_IDENTIFIERS))
as STMT, PROCEDURE_NAME, ACTIVATION_GROUP_NAME, OBJTEXT, V_CLIENT_IP_ADDRESS from INTERACTIVE_JOBS I, lateral
(select *
from table(QSYS2.STACK_INFO(JOBNAME)) J
where PROGRAM_LIBRARY_NAME not like 'Q%'
order by ORDINAL_POSITION desc limit 1)
X, lateral (select OBJTEXT
from table(QSYS2.OBJECT_STATISTICS(X.PROGRAM_LIBRARY_NAME, '*PGM *SRVPGM', X.PROGRAM_NAME)) as C)
as Y, lateral (select V_CLIENT_IP_ADDRESS
from table(QSYS2.GET_JOB_INFO(JOBNAME)) as D)
as Z order by CPU desc;
-Matt
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Thursday, February 28, 2019 4:46 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SPL question about error handling.
Some of these tables are IBM i services and require a parameter from the cursor. No, this parameter cannot be done with a JOIN or WHERE clause. It's a parameter to a UDTF.
Cursor is built around one IBM i service.
YetAnotherTable is another IBM i service which requires a parameter from the cursor. In our example let's call it fetchColumn1.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Thursday, February 28, 2019 6:37 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SPL question about error handling.
what's in YetAnotherTable?
select * doesn't seem to make sense in the context you've given...
Are you expecting to insert a single row or multiple rows into AnotherTable?
Charles
On Thu, Feb 28, 2019 at 4:35 PM Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
Rob,
Why do you need a cursor for this?
instead of
Insert into AnotherTable (
Select fetchColumn1, fetchColumn2, (select * from
YetAnotherTable));
does this work?
Insert into AnotherTable
values (fetchColumn1, fetchColumn2, (select * from
YetAnotherTable));
Charles
On Thu, Feb 28, 2019 at 4:23 PM Rob Berendt <rob@xxxxxxxxx> wrote:
Newbie to SPL.
Really trying to get the basics of error handling.
Basically I have a loop
Declare c1 cursor for select ...
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET ROW_NOT_FOUND = 1;
Open c1;
Fetch c1 into fetchColumn1, fetchColumn2...
SET END_CURSOR = ROW_NOT_FOUND;
WHILE END_CURSOR = 0 DO
Insert into AnotherTable (
Select fetchColumn1, fetchColumn2, (select * from
YetAnotherTable));
Note: rows are often not found in YetAnotherTable. In that case I
just want to skip this insert and fetch the next row from the cursor.
Set row_not_found = 0;
Fetch c1 into fetchColumn1, fetchColumn2...
Set END_CURSOR = row_not_found;
End while;
Close C1;
End p1;
The problem is that when it hits the missing row in YetAnotherTable,
it sets SQLCODE, SQLSTATE, ROW_NOT_FOUND, exits the loop and the procedure.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.dekko.com&d=D
wICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJh
t2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2bvBeB7olEYVr4M1mWSlPwdJQNtdNSwLLMaZ8
5sj4M&s=U5r0J1nXq7UKx-76N9FtGrv5DdHJzH0p2ditzyyZd10&e=
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.c
om_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTw
olPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2
bvBeB7olEYVr4M1mWSlPwdJQNtdNSwLLMaZ85sj4M&s=RVzwH55-R8jpVrUdWFWL2Sym5
BY9l28wF__HP5VALlY&e= or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2bvBeB7olEYVr4M1mWSlPwdJQNtdNSwLLMaZ85sj4M&s=DTwoofeVQ0LwGznMQ4CyhIdGRWJhnTtNroKg04n-0zI&e=.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our
affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.
com&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1T
l8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2bvBeB7olEYVr4M1mWSlPwdJQNtdNS
wLLMaZ85sj4M&s=nBDGWhl2fyu41btm0WO1aReHEyf_UWdJjzxmX6dLUeY&e=
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2bvBeB7olEYVr4M1mWSlPwdJQNtdNSwLLMaZ85sj4M&s=RVzwH55-R8jpVrUdWFWL2Sym5BY9l28wF__HP5VALlY&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2bvBeB7olEYVr4M1mWSlPwdJQNtdNSwLLMaZ85sj4M&s=DTwoofeVQ0LwGznMQ4CyhIdGRWJhnTtNroKg04n-0zI&e=.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2bvBeB7olEYVr4M1mWSlPwdJQNtdNSwLLMaZ85sj4M&s=nBDGWhl2fyu41btm0WO1aReHEyf_UWdJjzxmX6dLUeY&e=
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2bvBeB7olEYVr4M1mWSlPwdJQNtdNSwLLMaZ85sj4M&s=RVzwH55-R8jpVrUdWFWL2Sym5BY9l28wF__HP5VALlY&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2bvBeB7olEYVr4M1mWSlPwdJQNtdNSwLLMaZ85sj4M&s=DTwoofeVQ0LwGznMQ4CyhIdGRWJhnTtNroKg04n-0zI&e=.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=I2bvBeB7olEYVr4M1mWSlPwdJQNtdNSwLLMaZ85sj4M&s=nBDGWhl2fyu41btm0WO1aReHEyf_UWdJjzxmX6dLUeY&e=
midrange.com
