Tweet
I'm writing a simple UDTF with a cursor for loop and pipe() as a demo for less-sql-knowledgeable colleagues, and I'm encountering a strange compile issue that I don't understand.
Here is the source:
If I try and use one of the cursor output columns in the predicate of the IF statement, I get C compile errors:
Any thoughts?
Here is the source:
Code:
-- Requires TZTEST in library list to compile
create or replace function TZTEST/SQLTFPPF_TableFuncExample_SQLPLPipe (
filter1 varchar(20) default null
)
returns table (
Column1 varchar(20),
Column2 varchar(20),
Column3 varchar(20),
Column4 varchar(50)
)
specific SQLTFPPF
statement deterministic
reads sql data
set option dbgview = *source,
commit = *none,
output = *print
begin
declare Col1Out varchar(20);
declare Col2Out varchar(20);
declare Col3Out varchar(20);
declare Col4Out varchar(50);
for myCursor as
select Col1,
case when Col2 in ('3','4') then null else Col2 end as Col2,
Col3
from SQLEGPB1
-- Apply filter to col1. Unless it's null parameter is set which
-- for our purposes means match anything
where (
filter1 is null
or Col1 like '%'||filter1||'%'
)
do
-- Any SQL postprocessing of results?
-- Col1 is straight output
set Col1Out = Col1;
-- ***ERROR START***
-- This way of mapping Col2 to Col2Out works
set Col2Out = Col2;
if Col2Out in('3','4') then
set Col2Out = null;
end if;
-- This way of mapping Col2 to Col2Out produdes a C compile error
if Col2 in('3','4') then
set Col2Out = null;
else
set Col2Out = Col2;
end if;
-- ***ERROR END***
pipe(Col1,Col2,Col3,''); -- Write an output row
end for;
return;
end;
Code:
| 852
MSG ID SEV TEXT
<SEQNBR>-<FILE NO>:<FILE LINE NO>
CZM0058 30 Label has already been defined on line of "".
( 573-0:566 )
CZM1332 30 A function with return type "" may not return a value of type "".
( 782-0:774 )
CZM0006 30 Label is undefined.
( 850-0:839 )
Comment