Hi all,
I've tried to get primary and unique constraint information via syscat.tabconst and syscat.keycoluse tables as one value for each constraint.
For example, let's there is table called tblTest.
tblTest ( Id, col1, col2, col3, col4 )
- PK (P_PK_tblTest) : Id
- UK (U_UK_tblTest) : col2, col3
If I join two tables, syscat.tabconst (TC) and syscat.keycoluse (KC), it will show
TC.constname TC.type KC.colnames
-------------- -------- -------------
P_PK_tblTest P Id
U_UK_tblTest U col2
U_UK_tblTest U col3
But I'd like get them like below such as we can get columns information from syscat.indexes
TC.constname TC.type KC.colnames
-------------- -------- -------------
P_PK_tblTest P +Id
U_UK_tblTest U +col2+col3
So I've tried to solve this using function but it's complaining something.
How can I get the result like above (+col2+col3) ?
In addition, I've posted the script that I tried to make function and error message. It would return only one result.
create function getUPConstraint( p_schema varchar(50), p_tableName varchar(50), p_type varchar(1) ) returns varchar(200)
language sql
begin atomic
declare v_result varchar(200);
declare v_columns varchar(50);
declare c1 cursor with return for
SELECT kc.colname
FROM syscat.tabconst tc, syscat.keycoluse kc
WHERE tc.tabschema = kc.tabschema
AND tc.tabname = kc.tabname
AND tc.constname = kc. constname
and tc.tabschema = p_schema
and tc.tabname = p_tableName
AND tc.type = p_type
order by kc.colseq;
declare exit handler for not found
set v_result = '';
begin
open c1;
fetch_loop:
loop
fetch c1 into v_columns
set v_result = v_result + '+' + v_columns;
end loop fetch_loop;
close c1;
end;
return v_result ;
end@
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "cursor with return for SELEC" was
found following " declare c1". Expected tokens may include:
"<space>". LINE NUMBER=11. SQLSTATE=42601