CODEWITHSUNDEEP
user-defined-functionsdb2-400
Christopher Smith
Christopher Smith

Reputation: 205

Create DB2400 external user defined table function?

I'm trying to create a UDTF in DB2400

I have a ILE CL program I want to call. The program is created and runs like I want.

I create the UDTF

create function SMLFQA.XAJJUPC_LE(USERID CHAR(10))
    returns table (
                STATUS CHAR(3),
                USED DEC(7, 0),
                CREATED DEC(7, 0),
                SIGNON DEC(7, 0),
                EXCLUDE DEC(7, 0))
    EXTERNAL NAME 'SMLPQA/XAJJUPC_LE'
    specific XAJJUPC_LE
    language CL
    DISALLOW PARALLEL
    NO SQL
    PARAMETER STYLE DB2SQL

It apparently gets created

I try to run it

select * from table(smlfqa.xajjuc_le('xxxxx')) a

What I get

select * from table(smlfqa.xajjuc_le('xxxxx')) a

[SQL0204] XAJJUC_LE in SMLFQA type *N not found.


Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.

I'm pretty sure it creates because it does not let me create it again until I first drop it.

Any suggestions about what I'm doing wrong?

Upvotes: 0

Views: 46

Answers (1)

Charles
Charles

Reputation: 23793

String literals in SQL are varchar. You've defined the UDTF as needing a char parameter.

Since SQL allows for function overloading, it's looking for a function that accepts a varchar.

Either change the parm to varchar (which CL doesn't support), or cast the literal.

select * from table(smlfqa.xajjuc_le(char('xxxxx'))) a

Upvotes: 1

Related Questions