Toad World® Forums

UDF's do not display correctly if "in" used in parameter definition


#1

Message from: john.enevoldson_671

Hi, Take the following UDF: CREATE or replace FUNCTION “PXX”.“PFLFJE” ( “FTG”
CHARACTER(2), “LAGARTNR” CHARACTER(15), “TYP” CHARACTER(2) ) RETURNS
CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION
READS SQL DATA BEGIN return (values( select ‘1’ from syscat.routines fetch first
1 row only )); END; Compile in Toad - works fine. Then open the udf from the
explorer into an editor window. The udf shows as CREATE OR REPLACE FUNCTION
“PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN “TYP”
CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT
DETERMINISTIC EXTERNAL ACTION READS SQL DATA BEGIN return (values( select ‘1’
from syscat.routines fetch first 1 row only )); END; Note Toad has appended “in”
in front of the parameters. Compile - works ok. Refresh the object in the
explorer and then send to editor. Now the udf looks like: CREATE OR REPLACE
FUNCTION “PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN
“TYP” CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT
DETERMINISTIC EXTERNAL ACTION READS SQL DATASET SCHEMA = ‘SYSIBM’; The body is
missing - not good ! We are at db2 9.7 fp 2 on zlinux. Regards, John.


Historical Messages

Author: john.enevoldson_671
Date: Wed Aug 25 23:57:09 PDT 2010
Hi, Take the following UDF: CREATE or replace FUNCTION “PXX”.“PFLFJE” ( “FTG”
CHARACTER(2), “LAGARTNR” CHARACTER(15), “TYP” CHARACTER(2) ) RETURNS
CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION
READS SQL DATA BEGIN return (values( select ‘1’ from syscat.routines fetch first
1 row only )); END; Compile in Toad - works fine. Then open the udf from the
explorer into an editor window. The udf shows as CREATE OR REPLACE FUNCTION
“PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN “TYP”
CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT
DETERMINISTIC EXTERNAL ACTION READS SQL DATA BEGIN return (values( select ‘1’
from syscat.routines fetch first 1 row only )); END; Note Toad has appended “in”
in front of the parameters. Compile - works ok. Refresh the object in the
explorer and then send to editor. Now the udf looks like: CREATE OR REPLACE
FUNCTION “PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN
“TYP” CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT
DETERMINISTIC EXTERNAL ACTION READS SQL DATASET SCHEMA = ‘SYSIBM’; The body is
missing - not good ! We are at db2 9.7 fp 2 on zlinux. Regards, John.
__



#2

Message from: jpositano_718

John -

Which version of Toad for DB2 are you using? I tried this on the latest and the
script looks correct (w/body) after sending it to the editor.

Thanks
Jeff


Historical Messages

Author: Jeff Positano
Date: Fri Aug 27 06:26:25 PDT 2010
John -

Which version of Toad for DB2 are you using? I tried this on the latest and the
script looks correct (w/body) after sending it to the editor.

Thanks
Jeff

__

Author: john.enevoldson_671
Date: Wed Aug 25 23:57:09 PDT 2010
Message from: john.enevoldson_671 Hi, Take the following UDF: CREATE or replace
FUNCTION “PXX”.“PFLFJE” ( “FTG” CHARACTER(2), “LAGARTNR” CHARACTER(15), “TYP”
CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT
DETERMINISTIC EXTERNAL ACTION READS SQL DATA BEGIN return (values( select ‘1’
from syscat.routines fetch first 1 row only )); END; Compile in Toad - works
fine. Then open the udf from the explorer into an editor window. The udf shows
as CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN
“LAGARTNR” CHARACTER(15), IN “TYP” CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC
“PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION READS SQL DATA BEGIN
return (values( select ‘1’ from syscat.routines fetch first 1 row only )); END;
Note Toad has appended “in” in front of the parameters. Compile - works ok.
Refresh the object in the explorer and then send to editor. Now the udf looks
like: CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN
“LAGARTNR” CHARACTER(15), IN “TYP” CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC
“PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION READS SQL DATASET SCHEMA
= ‘SYSIBM’; The body is missing - not good ! We are at db2 9.7 fp 2 on zlinux.
Regards, John. _______________________________________ Historical Messages
Author: john.enevoldson_671 Date: Wed Aug 25 23:57:09 PDT 2010 Hi, Take the
following UDF: CREATE or replace FUNCTION “PXX”.“PFLFJE” ( “FTG” CHARACTER(2),
“LAGARTNR” CHARACTER(15), “TYP” CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC
“PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION READS SQL DATA BEGIN
return (values( select ‘1’ from syscat.routines fetch first 1 row only )); END;
Compile in Toad - works fine. Then open the udf from the explorer into an editor
window. The udf shows as CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” ( IN “FTG”
CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN “TYP” CHARACTER(2) ) RETURNS
CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION
READS SQL DATA BEGIN return (values( select ‘1’ from syscat.routines fetch first
1 row only )); END; Note Toad has appended “in” in front of the parameters.
Compile - works ok. Refresh the object in the explorer and then send to editor.
Now the udf looks like: CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” ( IN “FTG”
CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN “TYP” CHARACTER(2) ) RETURNS
CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION
READS SQL DATASET SCHEMA = ‘SYSIBM’; The body is missing - not good ! We are at
db2 9.7 fp 2 on zlinux. Regards, John. __


__



#3

John -

Which version of Toad for DB2 are you using? I tried this on the latest and the script looks correct (w/body) after sending it to the editor.

Thanks
Jeff