CREATE FUNCTION BINARY_TO_COMP3(VARBINARY(100), CHAR(5))
RETURNS DECIMAL(18)
EXTERNAL NAME 'MSUDF1'
LANGUAGE COBOL
PARAMETER STYLE DB2SQL
NO SQL
NO EXTERNAL ACTION
NOT DETERMINISTIC
RETURNS NULL ON NULL INPUT;
and here's the call I'm using
Quote:
select
binary_to_ebcdic(substr(dl1_segment1, 13, 5)) as HANDL
, binary_to_comp3(substr(dl1_segment1, 54, 5)) as LAANMAXV
, binary_to_comp3(substr(dl1_segment1, 99, 5)) as LAANBET
, binary_to_comp3(substr(dl1_segment1, 160, 3), 'ABCDE') as RAANTESATS
from TDRT3U0
order by extract_order asc
fetch first row only
I receive the following error message
Quote:
SQL Error [42884]: NO AUTHORIZED FUNCTION NAMED BINARY_TO_COMP3 HAVING COMPATIBLE ARGUMENTS WAS FOUND. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.31.10
Note that if I remove the ", CHAR(5)" from the create function, as well as the ", 'ABCDE'" from the select statement, it all works fine.
Any suggestions? _________________ Michael
Joined: 02 Dec 2002 Posts: 629 Topics: 176 Location: Stockholm, Sweden
Posted: Mon Sep 30, 2024 6:23 am Post subject:
Here's an example of a UDF that works.
Quote:
DROP FUNCTION BINARY_TO_EBCDIC ;
CREATE FUNCTION BINARY_TO_EBCDIC(VARBINARY(100))
RETURNS VARCHAR(100)
EXTERNAL NAME 'MSUDF3'
LANGUAGE COBOL
PARAMETER STYLE DB2SQL
NO SQL
NO EXTERNAL ACTION
NOT DETERMINISTIC
RETURNS NULL ON NULL INPUT;
You can see from the above that the actual function name is BINARY_TO_EBCDIC. In this example, MSUDF3 is the name of (in this case) the Cobol program that is called to perform the SQL processing. This can be seen in the first append I included where I wrote , binary_to_ebcdic(substr(dl1_segment1, 13, 5)) as HANDL
so the actual call isn't, in my eyes, incorrect.
Neiher is the
Quote:
CREATE FUNCTION BINARY_TO_COMP3(VARBINARY(100), CHAR(5))
RETURNS DECIMAL(18)
wrong, inasmuch as what I want to do (and maybe THAT'S the problem) that I want to send in two arguments, but only receive one back.
The second argument is intended to be a simple character string that will be analyzed in the COBOL program and result in how the decimal value is formatted and returned. For example, if the content is actually defined a 9V99 comp-3 then I know I need to divide the value (say 123) by 100 and return 1.23 (that in turn possibly opens up a can of worms regarding the definition in the RETURNS argument that's a different matter).
Basically, I'm wondering whether I can define a second argument as a character string that is passed in to the UDF. _________________ Michael
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
